Мера накопительной суммы с использованием виртуальных таблиц и RankX в DAX

Вопрос или проблема

Я пытаюсь создать измерение текущей суммы в DAX, которое изменяется динамически в зависимости от примененных фильтров. Текущая сумма должна накапливать значения в соответствии с рангом каждой строки. Я уже создал отдельные таблицы, и они работают, как и ожидалось. Однако, когда я пытаюсь реализовать это, используя измерение с виртуальными таблицами, оно не дает нужного результата. Вот код, над которым я работаю. Каждая часть, похоже, работает независимо (например, ранжирование и фильтрация), но в качестве комбинированного измерения она не возвращает правильную текущую сумму по рангу.

Running_sum_measure =

VAR tbl_1 =

ADDCOLUMNS(
FILTER(ALL(sales_table[CodigoArticulo]),[CQ1]>0)
,”porc”
,[PorcentajeParticipacionCQ1]
)

VAR tbl_1_sorted =
ADDCOLUMNS(
tbl_1,
“Rank”,
RANKX(
tbl_1,
[porc] + RAND() * 0.0001, // Добавление немного большего случайного компонента для обеспечения уникальности
,
DESC,
Dense
)
)

VAR table_rank =
ADDCOLUMNS(
tbl_1_sorted,

// Столбец “RowNumber” для назначения уникального номера строки каждой записи
“RowNumber”,
RANKX(
ALL(tbl_1_sorted), // Учитывает все записи таблицы без фильтров
[Rank] +
RANKX(
ALL(tbl_1_sorted),
CALCULATE(
MAXX(tbl_1_sorted, tbl_1_sorted[CodigoArticulo]) // Максимальное значение CodigoArticulo для разрешения споров
),
,
ASC,
Dense // Использует Dense для последовательных рангов
) / COUNTROWS(ALL(tbl_1_sorted)), // Корректировка для избежания ничьей на основе общего количества строк
,
ASC,
Dense // Использует восходящее и последовательное ранжирование для “RowNumber”
)
)

VAR final_ =
ADDCOLUMNS(
SUMMARIZE(
table_rank,
table_rank[CodigoArticulo],
table_rank[RowNumber],
“porc”, SUM(table_rank[porc])
),
“RunningSum”,
VAR CurrentRank = [RowNumber]
RETURN
CALCULATE(
SUM(table_rank[porc]),
FILTER(
ALL(table_rank),
table_rank[RowNumber] <= CurrentRank && table_rank[CodigoArticulo] = [CodigoArticulo] ) ) ) VAR running_current = SUMX(final_,[RunningSum]) RETURN running_current Начальная таблица будет выглядеть примерно так: CodigoArticulo porc abc234hj21 0.001269819292983 bc234hj21 0.023401928347459 bc234hj223 0.032123394040404 bc234hj24gn 0.011001110932900 Если кто-то имеет опыт работы с рангами с разрешением ничьих в таком измерении, я буду признателен за любой совет о том, как сделать так, чтобы эта текущая сумма работала динамически с фильтрами и рангами. Заранее спасибо за любую помощь, которую вы можете предоставить!

Ответ или решение

Создание меры для кумулятивной суммы (running sum) в DAX с учетом динамических фильтров и рангов возможно с использованием виртуальных таблиц и функции RANKX. Давайте рассмотрим, как можно организовать вашу меру и какие изменения могут помочь решить проблему.

Разбор задачи

Ваша цель заключается в том, чтобы создать кумулятивную сумму, которая будет учитывать порядок значений и динамически изменяться в зависимости от применяемых фильтров. Вы уже разработали фрагменты кода, которые работают независимо, однако при их комбинировании возникает проблема с кумулятивным расчетом.

Оптимизация кода DAX

1. Создание основной виртуальной таблицы:
   Правильно использовать ADDCOLUMNS и FILTER для получения нужной таблицы данных. Убедитесь, что ALL используется в правильных контекстах, чтобы избежать излишнего применения фильтров.

2. Rankx и Tie-Breakers:
   Учитывайте в вашем ранжировании возможные дубликаты и используйте случайный компонент для их различения, как вы и сделали с RAND(). При этом важно убедиться, что ранжирование выполняется корректно по нужной колонке.

3. Использование SUMX и FILTER для кумулятивного вычисления:
   Для достижения правильного результата в кумулятивной сумме можно улучшить логику фильтрации.

Вот потенциальный пересмотренный код вашей меры:

Running_sum_measure = 
VAR tbl_1 =
    ADDCOLUMNS(
        FILTER(ALL(sales_table[CodigoArticulo]), [CQ1] > 0),
        "porc", [PorcentajeParticipacionCQ1]
    )

VAR tbl_1_sorted =
    ADDCOLUMNS(
        tbl_1,
        "Rank", 
        RANKX(
            tbl_1, 
            [porc] + RAND() * 0.0001, 
            , 
            DESC, 
            Dense
        )
    )

VAR table_rank =
    ADDCOLUMNS(
        tbl_1_sorted,
        "RowNumber", 
        RANKX(
            tbl_1_sorted, 
            [Rank], 
            , 
            ASC, 
            Dense
        )
    )

VAR final_ =
    SUMMARIZE(
        table_rank,
        table_rank[CodigoArticulo],
        table_rank[RowNumber],
        "TotalPorc", SUM(table_rank[porc]) // Сумма по каждому CodigoArticulo
    )

RETURN 
    SUMX(
        final_,
        VAR CurrentRowNumber = [RowNumber]
        RETURN 
            CALCULATE(
                SUM(final_[TotalPorc]), 
                FILTER(
                    final_,
                    [RowNumber] <= CurrentRowNumber
                )
            )
    )

Основные изменения

• Удалена вложенность и упрощены вычисления, чтобы повысить читаемость и эффективность кода.
• SUMMARIZE теперь помогает группировать значения и одновременно вычислять сумму по каждой группе.
• Упрощена логика фильтрации для более прямого доступа к значениям текущего ряда, что позволит избежать проблемы со сложной иерархией фильтров.

Заключение

Данный подход к созданию накапливаемой суммы (running sum) в DAX позволяет сохранять гибкость и адаптацию под различные фильтры и ранжирование. Применяя вышеуказанные оптимизации, вы сможете обеспечить корректное функционирование вашей меры в зависимости от контекста. Рекомендуется протестировать новые изменения в среде Power BI, чтобы удостовериться в корректности расчетов.