Страницы: 1
RSS
Pover Pivot создать вычисляемый столбец итогов в свободной таблице, Как создать умный вычисляемый столбец итогов в power pivot
 
Добрый день! Не без помощи форумчан, Немного освоился с PQ решил попробовать что такое PP.
Собрал данные из в запрос, загрузил модель данных, а вот как итоги рассчитать не понимаю. Прилагаемые в сводной таблицы формулы расчета не подходят категорически. Нужен свой вычисляемый столбец видимо, или что-то похожее.

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

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

Если же решения в принципе нет, то как выйти из ситуации. Какие есть фарианты?
Изменено: lostandleft - 12.02.2020 13:53:10 (Простите, фаил не тот выгрузил, ошибка в формуле)
 
На примере расходов гсм сделал, остальное по аналогии.

PS вам бы исходные данные привести к нормальному виду с помощью Power query. Например, зачем там пропуски и Общие расходы, когда это можно посчитать в модели?
 
StepanWolkoff,  В общем, не понял на что я должен обратить внимание по Вашему примеру, и чему должен научиться. Попробую научиться понимаю того, что такое мера в общем смысле, но это не решение задачки.

Ваш пример не считает ни одну из строк корректно, к сожалению.

Пропуски в таблице данных просто для визуального отделения групп, не более, чтобы было представление о том, как выглядит результат по итоговому запросу PQ.
Выглядит это примерно также, но разумеется без пустых строк.
Цитата
StepanWolkoff написал:
зачем там пропуски и Общие расходы, когда это можно посчитать в модели?

Могу лишь отметить, что в реальном документе в строках суммы посчитаны на различные показатели строк, если все это переводить в привычный вид сводных таблиц, столбцов с группировками будет очень много, и все это будет нечитаемо.

Сама структура сводных таблиц не позволяет делать то, что можно сделать в PQ - формировать итоги по нескольким строкам
В реальном документе групп и сумм по группам около десятка, по разным строкам.
Руководству не нравится когда показатели в разных таблицах на листе, или тем более, в одной таблице с кучей столбцов содержащих группировки.
Поэтому все строки просчитаны в запросе, при этом итогов по столбцам нет.

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

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



ЗЫ StepanWolkoff,  посмотрел пример, не вижу никакой гибкости в нем. А Если строк со статьями расходов будет 70?   И они будут каждый день разными? А если название выгруженное поменяется?
Я согласен на уникальную формулу по 1-2 строкам, но не по всему списку. Это абсурд. Уникально ИМХО должно считаться только значение по количеству овощей, все остальное должно считаться автоматом.
Изменено: lostandleft - 12.02.2020 19:18:21
 
Цитата
lostandleft написал:
посмотрел пример, не вижу никакой гибкости в нем. А Если строк со статьями расходов будет 70?   И они будут каждый день разными? А если название выгруженное поменяется?
Для этого в правилах и написано, что прикладывать пример со структурой близкой к реальной, ну или хотя бы ставить в известность помогающих о ваших "особенностях" в данных.
У вас в голове образовалась некоторая каша в понятиях и назначении инструментов. PowerQuery - это прежде всего инструмент обработки данных, для приведения их в вид удобный для расчета в модели. PowerPivot - это инструмент для проведения расчетов на основе подготовленных данных.
В первом посте вы говорите:
Цитата
lostandleft написал:
В данном случае, мне необходимо рассчитать по одной из строк готовой сводной таблицы Сумму, по другим же строкам нужно рассчитать хитрое средневзвешенное значение.
Я вам точно также по шагам показал как можно рассчитать сумму по производству овощей и "хитрое средневзвешенное" по расходу ГСМ. Цифры полностью по этим двум строкам соответствуют вашим расчетам.
Если хотите полноценного решения - обращайтесь в раздел работа. Хотите совета - потрудитесь сделать качественный пример.
 
Цитата
StepanWolkoff написал:
Цифры полностью по этим двум строкам соответствуют вашим расчетам
К сожалению результат не правильный. Уберите последнюю дату, с помощью фильтра, в своем примере. И появляется ошибка. Должно быть 322.58, а в Вашем примере 285,71. С фильтрами Ваш пример не работает.
Хотя об этом я написал выше, и это для меня важно. Но Вы не решились прочитать моего сообщения и того, что было выделено жирным шрифтом, а именно:

Цитата
lostandleft написал:
итоговая задача сводится в моем примере к тому, чтобы посчитать среднее значение по различным датам.

У Вас неправильно считается "Удельный вес по дням", к сожалению. Суть этого поля - отражать удельный вес произведенных овощей за каждый день внутри выборки. Если Выбрали 3 дня - Удельный вес каждого дня из трех, выбрали 2 дня - удельный вес каждого дня из двух. И так далее.

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

В этом и есть вся соль.

Мой пример вполне конкретный, сделать так чтобы ПО ВСЕМ строкам были данные приведенные в столбце "Так должно быть"
Только первая строка имеет отличия, и она считается стандартной суммой, по всем остальным строкам должно быть средневзешенное значение.
Изменено: lostandleft - 13.02.2020 09:41:51
 
lostandleft, гонору бы вам поубавить и немного уважения к тем, кто вам помогает.
Изменено: StepanWolkoff - 13.02.2020 10:06:25
 
Цитата
StepanWolkoff написал:
немного уважения
Всегда обращался на Вы, ни разу не оскорбил...жаль если показалось иначе.
Спасибо за Ваше внимание к моей теме!
Если бы была кнопка СПАСИБО, я бы непременно ее нажал.
 
Разбираю вариант, предложенный StepanWolkoff,

не понимаю один момент. Фрагмент меры:

Средние расходы за период ГСМ:=SUMX(
  ALLSELECTED ( 'Таблица1'[Дата] ); [Сумма произведено овощей] / CALCULATE([Сумма произведено овощей]; ALLSELECTED('Таблица1'[Дата])) * ...

Насколькоя понимаю, и в числителе, и в знаменателе дроби содержится мера, к фильтрам которой применена функция ALLSELECTED('Таблица1'[Дата]). И вроде, и числитель, и знаменатель должны дать одинаковое значение, а дробь, соответственно должна равняться 1.

Вижу, что это не так. Но почему? Объясните, пожалуйста, буду очень благодарен.
 
Цитата
quasarrr написал:
И вроде, и числитель, и знаменатель должны дать одинаковое значение
Это не так. Функция SUMX это итератор, первый аргумент это таблица в которой он перебирает строки, а значит числитель это значение какой-то конкретной строки в таблице заданной первым аргументом. Знаменатель же здесь рассчитывается без учета контекста строки, так как он рассчитан при помощи CALCULATE с фильтром ALLSELECTED, т.е. здесь для каждой строки рассчитывается доля этой строки от суммы всего столбца.
Вот горшок пустой, он предмет простой...
 
PooHkrd,

Понял. Спасибо большое!
 
Вообще общую сумму в этом расчете я бы вынес в отдельную переменную, типа такого:
Код
Средние расходы за период ГСМ:=
var sum = CALCULATE([Сумма произведено овощей]; ALLSELECTED('Таблица1'[Дата]))
return
SUMX(
  ALLSELECTED ( 'Таблица1'[Дата] ); [Сумма произведено овощей] / sum * ...

т.к. сильно подозреваю что данное выражение пересчитывается каждый раз для каждой строки, а так мы его вычислим один раз и потом будем просто брать с полочки. Думаю так будет шустрее.
Вот горшок пустой, он предмет простой...
 
PooHkrd, согласен. в первой версии так и было))
 
Ну раз пошла такая пьянка...   ;)

Совет топикстартеру.
Там кое-что подсократить можно. Числитель со знаменателем. Наверно, тоже пошустрее будет.
 
Цитата
quasarrr написал:
Совет топикстартеру.
ТопикСтартеру бы с с этой головоломкой разобраться, что от куда фильтруется...
Код
Средние расходы за период ГСМ:=SUMX (
   ALLSELECTED ( 'Таблица1'[Дата] );
    [Сумма произведено овощей]/CALCULATE([Сумма произведено овощей];ALLSELECTED('Таблица1'[Дата])) *( [Сумма итого]
/[Сумма произведено овощей]
*[Сумма произведено овощей]/CALCULATE([Сумма произведено овощей];ALLSELECTED('Таблица1'[Дата])))
)* CALCULATE ( [Сумма произведено овощей]; ALLSELECTED ( 'Таблица1'[Дата] ) )
И с вот таким очарованием:
Код
||HASONEVALUE
Мало того, что функция непонятная, похоже что без аналога в эксель, так еще и 2 палки.

Потом уж об оптимизациях думать.
 
lostandleft, стоп, как это без аналога в Excel? А в чем я сделал тогда?
|| - это аналог функции OR=ИЛИ. Соответственно && - это аналог AND=И.
HASONEVALUE - проверяет контекст возвращает одно значение или нет и возвращает логическое True/False
 
StepanWolkoff, Спасибо за пояснение. С Условием CASE более менее ясно.
Страницы: 1
Читают тему (гостей: 1)
Наверх