Добрый день! Не без помощи форумчан, Немного освоился с PQ решил попробовать что такое PP. Собрал данные из в запрос, загрузил модель данных, а вот как итоги рассчитать не понимаю. Прилагаемые в сводной таблицы формулы расчета не подходят категорически. Нужен свой вычисляемый столбец видимо, или что-то похожее.
Думаю что я не понимаю что такое мера, что такое вычисляемый столбец, и как все это заставить в итоге работать. К сожалению PQ дает ряд ограничений на вычисления, в частности оно не дает динамично работать с фильтрами.
В данном случае, мне необходимо рассчитать по одной из строк готовой сводной таблицы Сумму, по другим же строкам нужно рассчитать хитрое средневзвешенное значение. Фаил пример во вложении. Пожалуйста поясните возможно ли это в принципе.
Если же решения в принципе нет, то как выйти из ситуации. Какие есть фарианты?
На примере расходов гсм сделал, остальное по аналогии.
PS вам бы исходные данные привести к нормальному виду с помощью Power query. Например, зачем там пропуски и Общие расходы, когда это можно посчитать в модели?
StepanWolkoff, В общем, не понял на что я должен обратить внимание по Вашему примеру, и чему должен научиться. Попробую научиться понимаю того, что такое мера в общем смысле, но это не решение задачки.
Ваш пример не считает ни одну из строк корректно, к сожалению.
Пропуски в таблице данных просто для визуального отделения групп, не более, чтобы было представление о том, как выглядит результат по итоговому запросу PQ. Выглядит это примерно также, но разумеется без пустых строк.
Цитата
StepanWolkoff написал: зачем там пропуски и Общие расходы, когда это можно посчитать в модели?
Могу лишь отметить, что в реальном документе в строках суммы посчитаны на различные показатели строк, если все это переводить в привычный вид сводных таблиц, столбцов с группировками будет очень много, и все это будет нечитаемо.
Сама структура сводных таблиц не позволяет делать то, что можно сделать в PQ - формировать итоги по нескольким строкам В реальном документе групп и сумм по группам около десятка, по разным строкам. Руководству не нравится когда показатели в разных таблицах на листе, или тем более, в одной таблице с кучей столбцов содержащих группировки. Поэтому все строки просчитаны в запросе, при этом итогов по столбцам нет.
С другой стороны, сводные таблицы позволяют пользоваться фильтрами, итоговая задача сводится в моем примере к тому, чтобы посчитать среднее значение по различным датам. Выбирая разные значения дат, мы, в моем примере видим разные результаты.
Все что я посчитал можно было сделать проще в расчетной формуле, но я специально показываю все этапы вычислений чтобы было понятно как именно считается средняя.
ЗЫ StepanWolkoff, посмотрел пример, не вижу никакой гибкости в нем. А Если строк со статьями расходов будет 70? И они будут каждый день разными? А если название выгруженное поменяется? Я согласен на уникальную формулу по 1-2 строкам, но не по всему списку. Это абсурд. Уникально ИМХО должно считаться только значение по количеству овощей, все остальное должно считаться автоматом.
lostandleft написал: посмотрел пример, не вижу никакой гибкости в нем. А Если строк со статьями расходов будет 70? И они будут каждый день разными? А если название выгруженное поменяется?
Для этого в правилах и написано, что прикладывать пример со структурой близкой к реальной, ну или хотя бы ставить в известность помогающих о ваших "особенностях" в данных. У вас в голове образовалась некоторая каша в понятиях и назначении инструментов. PowerQuery - это прежде всего инструмент обработки данных, для приведения их в вид удобный для расчета в модели. PowerPivot - это инструмент для проведения расчетов на основе подготовленных данных. В первом посте вы говорите:
Цитата
lostandleft написал: В данном случае, мне необходимо рассчитать по одной из строк готовой сводной таблицы Сумму, по другим же строкам нужно рассчитать хитрое средневзвешенное значение.
Я вам точно также по шагам показал как можно рассчитать сумму по производству овощей и "хитрое средневзвешенное" по расходу ГСМ. Цифры полностью по этим двум строкам соответствуют вашим расчетам. Если хотите полноценного решения - обращайтесь в раздел работа. Хотите совета - потрудитесь сделать качественный пример.
StepanWolkoff написал: Цифры полностью по этим двум строкам соответствуют вашим расчетам
К сожалению результат не правильный. Уберите последнюю дату, с помощью фильтра, в своем примере. И появляется ошибка. Должно быть 322.58, а в Вашем примере 285,71. С фильтрами Ваш пример не работает. Хотя об этом я написал выше, и это для меня важно. Но Вы не решились прочитать моего сообщения и того, что было выделено жирным шрифтом, а именно:
Цитата
lostandleft написал: итоговая задача сводится в моем примере к тому, чтобы посчитать среднее значение по различным датам.
У Вас неправильно считается "Удельный вес по дням", к сожалению. Суть этого поля - отражать удельный вес произведенных овощей за каждый день внутри выборки. Если Выбрали 3 дня - Удельный вес каждого дня из трех, выбрали 2 дня - удельный вес каждого дня из двух. И так далее.
В Вашем примере берется производство овощей за день и всегда делится на общее производство за весь период, а не за период выборки. В том случае, если мы выбираем в фильтре один день, то значения в итоговом столбце должны быть равны значению выборки.
В этом и есть вся соль.
Мой пример вполне конкретный, сделать так чтобы ПО ВСЕМ строкам были данные приведенные в столбце "Так должно быть" Только первая строка имеет отличия, и она считается стандартной суммой, по всем остальным строкам должно быть средневзешенное значение.
Всегда обращался на Вы, ни разу не оскорбил...жаль если показалось иначе. Спасибо за Ваше внимание к моей теме! Если бы была кнопка СПАСИБО, я бы непременно ее нажал.
Средние расходы за период ГСМ:=SUMX( ALLSELECTED ( 'Таблица1'[Дата] ); [Сумма произведено овощей] / CALCULATE([Сумма произведено овощей]; ALLSELECTED('Таблица1'[Дата])) * ...
Насколькоя понимаю, и в числителе, и в знаменателе дроби содержится мера, к фильтрам которой применена функция ALLSELECTED('Таблица1'[Дата]). И вроде, и числитель, и знаменатель должны дать одинаковое значение, а дробь, соответственно должна равняться 1.
Вижу, что это не так. Но почему? Объясните, пожалуйста, буду очень благодарен.
quasarrr написал: И вроде, и числитель, и знаменатель должны дать одинаковое значение
Это не так. Функция SUMX это итератор, первый аргумент это таблица в которой он перебирает строки, а значит числитель это значение какой-то конкретной строки в таблице заданной первым аргументом. Знаменатель же здесь рассчитывается без учета контекста строки, так как он рассчитан при помощи CALCULATE с фильтром ALLSELECTED, т.е. здесь для каждой строки рассчитывается доля этой строки от суммы всего столбца.
Вообще общую сумму в этом расчете я бы вынес в отдельную переменную, типа такого:
Код
Средние расходы за период ГСМ:=
var sum = CALCULATE([Сумма произведено овощей]; ALLSELECTED('Таблица1'[Дата]))
return
SUMX(
ALLSELECTED ( 'Таблица1'[Дата] ); [Сумма произведено овощей] / sum * ...
т.к. сильно подозреваю что данное выражение пересчитывается каждый раз для каждой строки, а так мы его вычислим один раз и потом будем просто брать с полочки. Думаю так будет шустрее.
lostandleft, стоп, как это без аналога в Excel? А в чем я сделал тогда? || - это аналог функции OR=ИЛИ. Соответственно && - это аналог AND=И. HASONEVALUE - проверяет контекст возвращает одно значение или нет и возвращает логическое True/False