Вопрос по Power Pivot - чем заменить функцию агрегирования таблицы SUMMARIZE на более быструю

RSS

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

ЮлияС

Пользователь

Сообщений: 10 Регистрация: 24.12.2019

09.10.2023 14:06:50

Добрый день.
Помогите, пожалуйста, усовершенствовать формулу DAХ, т.к. текущая очень долго пересчитывается, при работе со срезами приходится ждать по 20-30 секунд, чтобы Сводная отобразила результаты (строк в базе данных более 5 млн).

Необходимо вывести в сводной среднее количество проданных СКЮ на 1 Торговую Точку (далее ТТ) помесячно, при чем итоги по году должны быть так же агрегированы без суммирования по месяцам.
1. Я посчитала сумму отгруженных СКЮ на каждую торговую точку, а чтобы итоги за год у меня не суммировались по месяцам, я использовала функцию агрегирования.
Сумма отгруженных СКЮ на каждую ТТ:=COUNTAX(ADDCOLUMNS(
SUMMARIZE('Таблица1';'Таблица1'[Год];'Таблица1'[ТТ];'Таблица1'[СКЮ Код]);
"СКЮ, шт";CALCULATE(COUNT('Таблица1'[СКЮ Код])));[СКЮ, шт])
2. Посчитала кол-во уникальных ТТ:
Кол-во уникал.ТТ:=DISTINCTCOUNT('Таблица1'[ТТ])
3. Посчитала среднее кол-во СКЮ на ТТ:
Среднее Кол-во СКЮ на ТТ:=DIVIDE([Сумма отгруженных СКЮ на каждую ТТ];[Кол-во уникал.ТТ];BLANK())

и все работает. НО! функция SUMMARIZE, примененная в 1 пункте, очень и очень долго пересчитывает. Невозможно работать с фильтрами в сводной таблице. Пыталась заменить на SUMMARIZECOLUMNS, но постоянно выдает ошибку: SummarizeColumns() и AddMissingItems() нельзя использовать в этом контексте.

Помогите, пожалуйста, найти более оперативное решение.
Пример файла прилагаю.

Прикрепленные файлы

для примера Dax.xlsx (182.65 КБ)

surkenny

Пользователь

Сообщений: 3005 Регистрация: 13.06.2014

09.10.2023 17:01:31

ЮлияС, SUMMARIZE быстрая функция. Аналогичный результат можно получить c помощью GROUPBY, но быстрее не будет.
Вы просто написали очееень странную меру:
Что Вы пытаетесь посчитать функцией COUNTAX()? Полагаю, что Вам нужно сумму по столбцу [СКЮ, шт]
И зачем в SUMMARIZE год?
Если в SUMMARIZE у Вас 'Таблица1'[СКЮ Код], то зачем вообще вычислять то, что у Вас в CALCULATE? Там всегда 1.
И, наверное, DISTINCTCOUNT, а не COUNT.
Уже мера попроще:

Код

Сумма отгруженных СКЮ на каждую ТТ :=
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'Таблица1'; 'Таблица1'[ТТ] );
        "СКЮ, шт"; CALCULATE ( DISTINCTCOUNT ( 'Таблица1'[СКЮ Код] ) )
    );
    [СКЮ, шт]
)

Можно даже ADDCOLUMNS исключить и не использовать SUMMARIZE:
1.

Код
Сумма отгруженных СКЮ на каждую ТТ := SUMX ( DISTINCT ( 'Таблица1'[ТТ] ); CALCULATE ( DISTINCTCOUNT ( 'Таблица1'[СКЮ Код] ) ) )

А можно пойти вообще с другой стороны. Вы для каждого сочетания ТТ-SKU добавляли 1 и считали число этих 1. Так можно же просто посчитать число трок таблицы:
2.

Код
Сумма отгруженных СКЮ на каждую ТТ := COUNTROWS ( SUMMARIZE ( 'Таблица1'; 'Таблица1'[ТТ]; 'Таблица1'[СКЮ Код] ) )

Варианты 1 и 2 дадут одинаковый результат. Но в первом варианте небольшой объем строк виртуальной таблицы (число строк равно числу ТТ), но побольше вычислений. Во втором варианте мало вычислений, но более объемная таблица (число строк равно число сочетаний ТТ-SKU).
* Результат на самом деле может быть разным: к примеру, когда у каких-то строк будет пустой СКЮ Код. Но, скорее всего, и ТТ, и SKU у Вас всегда заполнены.
Протестируйте оба варианта на производительность.

А уже самостоятельно подумайте, что посчитается таким выражением:

Код
AVERAGEX ( VALUES ( 'Таблица1'[ТТ] ); CALCULATE ( DISTINCTCOUNT ( 'Таблица1'[СКЮ Код] ) ) )

Изменено: surkenny - 10.10.2023 09:47:45

Читают тему