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

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

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

Выдает в сводной интересующий вас результат.
Принцип действия такой
функция SUMMARIZE создает виртуальную сводную без итогов по измерению Регион и создает виртуальный столбец Макс, который содержит агрегированные значения. Т.е. это та самая сводная, что имеется в вашем примере, но без строки итогов. Далее функция SUMX суммирует строки в столбце МАКС уже этой виртуальной сводной таблицы, но на результат строк по измерению регион это никак не влияет, ибо для них есть только одна рассчитанная строка, а вот на итоги и, если они будут, промежуточные итоги это уже влияет и получается результат, который вы и хотели.
Изменено: PooHkrd - 13.04.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.04.2018 17:35:43
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
крайне тормозная на больших объемах и большом количестве группировочных столбцов
насколько большой объем?
F1 творит чудеса
 
10 млн строк собираются из десятка xlsx файлов. Группировать приходилось по 5 столбцам. Все виснет нафиг. Выход нашел такой: сначала объединяю эти столбцы в один, делаю группировку, потом уже разъединяют обратно. Скрипт работает минут 7-10, но хоть не виснет. Благо, что это был разовый расчет. А не регулярный.
Изменено: PooHkrd - 16.04.2018 15:49:15
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
10 млн строк собираются из десятка xlsx файлов.
группировка в каждом отдельном файле делалась, или на объединенном массиве?
F1 творит чудеса
 
На объединенном.
Вот горшок пустой, он предмет простой...
 
Ну, странно, хотя 10 млн это уже объем, конечно. А если в таблицу ключи добавить перед группировкой - должно, по идее, быстрее. Хотя подозреваю, что, когда речь идет о файлах как источнике данных, идет многократное обращение к источнику или кэширование на диск - фолдить-то некуда, а в памяти может вдруг не поместиться.
F1 творит чудеса
 
В памяти, на удивление помещается, больше трёх мэшап-контейнеров в процессах не наблюдал. Считывается один раз, потом там зачищаю дубликаты, делаю расчетные столбцы, джойню кое-чего и все это дело в буфер. А дальше как указал, если объединить столбцы и по одному полю группировать, потом разъединить то все гут, хотя и не быстро. А если просто по 5 столбцам - почему-то умирает, хотя на мой ламерский взгляд группировка должна примерно по такому же алгоритму работать. Странно это всё.
Изменено: PooHkrd - 19.04.2018 07:02:35
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
и все это дело в буфер
если объем данных большой, то буфер может обернуться другой стороной - все, что не помещается в буфер, сбрасывается на диск и потом читается оттуда, короче, все становится только хуже. буфер действует только в рамках одного процесса. Так что не все надо в буфер пихать, может быть чревато. Попробуйте без буфера сделать группировку
F1 творит чудеса
 
Без него с группировкой по 5 столбцам та же ерунда, а со вторым вариантом дольше работает запрос, ибо 2 раза обращается к исходникам. Собственно именно из-за этого и попробовал буфер. Стало шустрее. На самом деле такие ситуации - это редкость, максимум группируются по двум полям. Но когда столкнулся, блин, целый день убил на нащупывание разных вариантов решения. Не факт, что нашел самое  оптимальное, но для меня главное что работает и при этом корректно.
Вот горшок пустой, он предмет простой...
 
Добрый день, есть вопрос по данной теме:
Можно ли сделать чтобы в сводной в значениях считалось кол-во в шт а в промежуточных итогах кол-во уникальных элементов
 
Цитата
levWel написал:
Можно ли сделать чтобы в сводной в значениях считалось кол-во в шт а в промежуточных итогах кол-во уникальных элементов
можно в сводной, построенной в Power Pivot
 
Как создать такую Меру ? получается есть колонка продажи в шт. и есть колонка SKU. Мне необходимо чтобы в значениях были продажи в шт , а в пром. итоге кол-во уникальных значений по SKU. Все это в разрезе торговых точек
Изменено: levWel - 29.05.2023 14:08:22
 
levWel, IF, ISFILTERED, SUM, COUNTROWS, VALUES
Пришелец-прораб.
Страницы: 1
Наверх