Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Раздельные формулы у вычисляемого поля в сводной для значений и итогов, Для подсчёта суммы максимумов (случай денормализованных исходных данных)
 
Есть данные в таблице, в которой столбцы с данными привязаны к разным метрикам, но по которых требуется построить единую сводную.
Вот пример:

Есть некая выборка Торговых Точек (ТТ) (таблица слева), и сводная по ней (справа). Каждая ТТ относится к какому-то региону, и регион имеет значение некоего числового параметра: "Параметр региона" привязан к региону, и одинаковый для разных Торговых точек (ТТ) одного региона.
"Параметр ТТ" привязан к ТТ.
Сумму в сводной по региону для "параметра ТТ" считается без проблем
А вот сумма "параметра региона" считается некорректно, что я только не делал: либо складывается одно значение несколько раз (если расчётное поле - сумма), что совсем неверно, либо если расчётное поле - максимум / минимум, то общий итог считается некорректно. Как можно обходите такую простую, казалось бы, проблему?

Я знаю, что можно изменить исходную таблицу, сделав вывод значения "параметр региона" не одним и тем же значением, а в одной ТТ (скажем, 101) выводить 100 как параметр, а в других ТТ этого же региона не выводить ничего. Тогда сумма в сводной будет считаться верно, но тогда данные в исходной таблице будет некорректными, а так же будет проблема возможного фильтра в сводной (если кто-то добавит фильтр, который отсечёт строку со значением параметра региона, то значение потеряется, что некорректно). Итого приведённое мною решение костыльное и проблемное, есть ли разумные решения?
 
Вам нужно смотреть в сторону PowerPivot и формул DAX. Вот только я, к сожалению, не силен в написании формул для строк итогов и промежуточных итогов. Тут есть сильные товарищи, возможно они к вам заглянут на огонек сегодня. Что у вас за версия Excel?
 
Цитата
PooHkrd написал:
Вам нужно смотреть в сторону PowerPivot и формул DAX
Интересно, попробую поискать, но пока не представляю - как это  :)  . Спасибо за наводку!
Цитата
PooHkrd написал:
Что у вас за версия Excel?
Excel 2010  :(  
 
Можете пока отсюда скачать и попробовать установить.
 
Короче покумекал тут, получил вот такую формулу для меры:
Код
Мера:=
SUMX(
   SUMMARIZE(
      'Таблица1';
      [Регион];
      "Макс";
      MAX([Параметр региона]));
   [Макс])

Выдает в сводной интересующий вас результат.
Принцип действия такой
функция SUMMARIZE создает виртуальную сводную без итогов по измерению Регион и создает виртуальный столбец Макс, который содержит агрегированные значения. Т.е. это та самая сводная, что имеется в вашем примере, но без строки итогов. Далее функция SUMX суммирует строки в столбце МАКС уже этой виртуальной сводной таблицы, но на результат строк по измерению регион это никак не влияет, ибо для них есть только одна рассчитанная строка, а вот на итоги и, если они будут, промежуточные итоги это уже влияет и получается результат, который вы и хотели.
Изменено: PooHkrd - 13 Апр 2018 12:35:59
 
Блин, как круто, спасибо огромное!  
 
файл не могу посмотреть.
если в итогах нужна сумма максимумов по регионам, то формула такая
Код
Сумма максимумов =
SUMX (
    VALUES ( 'Таблица1'[Регион] );
    CALCULATE ( MAX ( 'Таблица1'[Параметр региона] ) )
)

виртуальную сводную здесь городить не надо, если таблица, которую перебирает SUMX, состоит из одного столбца. Достаточно сразу скормить ей регион
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
виртуальную сводную здесь городить не надо, если таблица, которую перебирает SUMX, состоит из одного столбца.
Привет, Максим.
А если вместо Values поставить Summarize?
 
Андрей VG, с точки зрения работы SUMX - вообще без разницы, перебираться будет в итоге таблица, состоящая из одного столбца уникальных значений. Но итальянцы говорят, что SUMMARIZE не самая эффективная функция :) и, например, всегда рекомендуют использовать
Код
ADDCOLUMNS(SUMMARIZE(Table, Column), "name", expression) 

вместо
Код
SUMMARIZE(Table, Column, "name", expression)

мол, быстрее работает. Ну а в последних версиях вообще говорят только о SUMMARIZECOLUMNS - еще эффективнее и удобнее.

Построение виртуальной сводной здесь в принципе ненужная операция - SUMX создает контекст строки, который затем CALCULATE превращает в контекст фильтра, и оно все само группируется по текущей строке первого аргумента.

На небольших объемах, конечно, разница незаметна.
F1 творит чудеса
 
Прям беда какая-то с этими группировками, что в PQ Table.Group крайне тормозная на больших объемах и большом количестве группировочных столбцов (по крайней мере в Экселе), теперь оказывается еще и SUMMARIZE туда же. Ну в DAX-то хоть объездные маневры есть, а вот в PQ без Table.Group прямо тяжко.
Изменено: PooHkrd - 13 Апр 2018 17:35:43
 
Цитата
PooHkrd написал:
крайне тормозная на больших объемах и большом количестве группировочных столбцов
насколько большой объем?
F1 творит чудеса
 
10 млн строк собираются из десятка xlsx файлов. Группировать приходилось по 5 столбцам. Все виснет нафиг. Выход нашел такой: сначала объединяю эти столбцы в один, делаю группировку, потом уже разъединяют обратно. Скрипт работает минут 7-10, но хоть не виснет. Благо, что это был разовый расчет. А не регулярный.
Изменено: PooHkrd - 16 Апр 2018 15:49:15
 
Цитата
PooHkrd написал:
10 млн строк собираются из десятка xlsx файлов.
группировка в каждом отдельном файле делалась, или на объединенном массиве?
F1 творит чудеса
 
На объединенном.
 
Ну, странно, хотя 10 млн это уже объем, конечно. А если в таблицу ключи добавить перед группировкой - должно, по идее, быстрее. Хотя подозреваю, что, когда речь идет о файлах как источнике данных, идет многократное обращение к источнику или кэширование на диск - фолдить-то некуда, а в памяти может вдруг не поместиться.
F1 творит чудеса
 
В памяти, на удивление помещается, больше трёх мэшап-контейнеров в процессах не наблюдал. Считывается один раз, потом там зачищаю дубликаты, делаю расчетные столбцы, джойню кое-чего и все это дело в буфер. А дальше как указал, если объединить столбцы и по одному полю группировать, потом разъединить то все гут, хотя и не быстро. А если просто по 5 столбцам - почему-то умирает, хотя на мой ламерский взгляд группировка должна примерно по такому же алгоритму работать. Странно это всё.
Изменено: PooHkrd - 19 Апр 2018 07:02:35
 
Цитата
PooHkrd написал:
и все это дело в буфер
если объем данных большой, то буфер может обернуться другой стороной - все, что не помещается в буфер, сбрасывается на диск и потом читается оттуда, короче, все становится только хуже. буфер действует только в рамках одного процесса. Так что не все надо в буфер пихать, может быть чревато. Попробуйте без буфера сделать группировку
F1 творит чудеса
Страницы: 1
Читают тему (гостей: 2)