Страницы: 1 2 След.
RSS
План-фактный анализ при помощи сводной таблицы без добавления дополнительных расчетных столбцов в исходном массиве, вычисляемое поле в сводной таблице
 
В исходной таблице в одной колонке "Статус" указан план или факт.
Необходимо в сводной по данной таблице рассчитать разницу между фактическими данными и плановыми.
Пока получилось вручную посчитать разницу.
 
Цитата
Viper25 написал: Необходимо в сводной по данной таблице
Так в сводной или же целесообразнее в самой таблице, как говорится, на месте выявить разницу, и ее без заморочек выводить в сводной?.. ;)
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
Именно в сводной должны быть расчеты, чтобы исходную не нагружать.
 
Вариант при помощи PowerPivot и наборов полей. Пример формулы для меры по количеству:
Код
Разность План_Факт Количество:=
SUMX(
   FILTER('Таблица1';'Таблица1'[Статус]="Факт");
   [Кол-во]) - 
SUMX(
   FILTER('Таблица1';'Таблица1'[Статус]="План");
   [Кол-во])
Вот горшок пустой, он предмет простой...
 
PooHkrd, можно ли добавить среднюю ставку в такую сводную?
В обычной сводной можно было создать вычисляемое поле.
А тут как?

Еще вопрос: "В сводной появилась группа Наборы".
Подскажите, как Вы ее создали.
Изменено: Viper25 - 12.04.2018 12:52:09
 
Цитата
Viper25 написал:
можно ли добавить среднюю ставку в такую сводную?
Можно, покажите логику расчетов также как показали формулами в предыдущем примере. У вас что за Excel? Вы модель данных и формулы, которые я написал можете видеть?
По наборам, сначала создаете сводную с набором столбцов и расчетных полей, потом нужно нажать на любую ячейку сводной и на ленте во вкладке "Анализ" жмите по кнопке "Поля, элементы и наборы" - > "Создать набор по столбцам". Там отобразятся все поля, которые имются в сводной на данный момент. В этой менюшке можно удалять ненужные вам столбцы и сохранить такое состояние под отдельным именем.
Вот горшок пустой, он предмет простой...
 
Нашел у себя ошибку - все ячейки должны быть заполнены цифрами.
(А у меня были ячейки с пустыми значениями.)
Помогите среднюю ставку в сводную добавить и отклонение по ней.
 
Цитата
Viper25 написал:
ставку в сводную добавить и отклонение по ней.
Что вы понимаете под средней ставкой? Как бы вы её посчитали вручную? Обрисуйте алгоритм - вам помогут. Формулы вы, как я погляжу, быстро схватили.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Что вы понимаете под средней ставкой?
[Доход, тыс. дол.] / [Кол-во] * 1000
 
Это ж вроде цена за штуку получается. Разве нет? Зачем её суммировать и потом считать разность в сводной? К тому же у вас формула эта уже реализована.
Может в формуле нужно задействовать столбец [Ставка, дол.] ?
А ошибка #ЧИСЛО получается из-за деления на ноль.
Как это работает : функция SUMX сначала рассчитывает указанное вами выражение для каждой строки, а потом суммирует полученные результаты с учетом фильтров сводной таблицы. Чтобы избежать таких проблем имеется функция DIVIDE(). Таким образом, если я все правильно понял, то ваша формула для отклонения должна выглядеть так:
Код
Отклонение Цены, дол:=
SUMX(
   FILTER('Объемные';[План/Факт]="Факт");
   DIVIDE([Доход, тыс. дол.];[Кол-во];0)*1000) - 
SUMX(
   FILTER('Объемные';[План/Факт]="План");
   DIVIDE([Доход, тыс. дол.];[Кол-во];0)*1000)
Изменено: PooHkrd - 12.04.2018 16:46:34
Вот горшок пустой, он предмет простой...
 
В таблице нет колонки "Ставка".
Ставка, дол:=SUMX('Объемные';DIVIDE([Доход, тыс. дол.];[Кол-во];0)*1000)
По плану средняя ставка должна быть 2929/351543*1000 = 8,33!
А не получается.

Разобрался.
Ставка, дол:=SUM([Доход,тыс. дол. Итого])/sum([Кол-во])*1000
Изменено: Viper25 - 12.04.2018 17:55:49
 
Так что ли?
Вот горшок пустой, он предмет простой...
 
Да, так.
Еще функцию DIVIDE() придется внедрить.
 
Ну, это уж сами. Главное, что суть ухватили. Нужно четко объяснять: сначала суммируем все строки, потом что делаем с полученными суммами, или сначала считаем выражение для конкретной строки, а уж потом суммируем эти результаты.
Вот горшок пустой, он предмет простой...
 
Можно ли задать формат для показателей Power Pivot?
Например, чтобы Отклонение Цены выводилось с одним знаком после запятой.
Если задать формат ячейки на листе, то при обновлении сводной формат слетает.

Нашел как. Через условное форматирование.
Изменено: Viper25 - 13.04.2018 11:36:26
 
Для таблицы в Power Pivot создал расчетные столбцы.
Создал набор полей для сводной.
Но расчетные столбцы по отклонениям и выполнению в сводной не отображаются.
Не могу разобраться почему.
Изменено: Viper25 - 15.05.2018 17:25:53
 
Если по уму это уже другой вопрос для отдельной темы. Модераторы могут потереть помощь.
Вот горшок пустой, он предмет простой...
 
Задача как первоначальная, но осложнена тем, что сводная строится не на основании таблицы на листе, а на основании модели данных.
Т.е. исходная таблица, в которой в одной колонке "Статус" указан план или факт, загружена и обработана в Power Query.
Выгрузить запрос на лист нельзя (превышает 1 млн.строк).
Поэтому загружен запрос в модель данных.
Необходимо в сводной рассчитать разницу между фактическими данными и плановыми.
 
Не понял в чем сложность, в предыдущих примерах я сводную также строил на основании модели данных, а таблица в модель загонялась из таблицы на листе. Какая разница каким образом вы внесли таблицу в модель данных и строите на её основе сводную?
Так-то формула элементарная:
Код
План_Фактный Анализ:=CALCULATE(SUM([Доход]);'Исходник'[Статус]="План") - CALCULATE(SUM([Доход]);'Исходник'[Статус]="Факт")
Изменено: PooHkrd - 31.05.2018 11:37:09
Вот горшок пустой, он предмет простой...
 
PooHkrd, таблица на листе совсем другая. В ней нет колонки "Статус". Эта колонка добавляется в Power Query.
 
Viper25, опять не понимаю какая разница. Формулы DAX работают с тем, что вы загрузили в модель, что было в источниках не имеет значения.
Формула то подходит?
Вот горшок пустой, он предмет простой...
 
PooHkrd, получилось. Оказывается, надо было создать меру в мастере, выбрав нужную таблицу.
Спасибо.
 
Вроде бы написал одно и то же.Но с оператором CALCULATE получаю ошибку.
Код
ДоходФакт_1:=CALCULATE(SUM([Доход]);FILTER('Исходник'[Статус]="Факт"))
ДоходФакт_2:=SUMX(FILTER('Исходник';[Статус]="Факт");[Доход]) 
Изменено: Viper25 - 12.06.2018 18:31:28
 
Цитата
Viper25 написал:
Вроде бы написал одно и то же
Да, ну, обман
Цитата
Viper25 написал:
FILTER('Исходник'[Статус]="Факт")
и
Цитата
Viper25 написал:
FILTER('Исходник';[Статус]="Факт")
 
Андрей VG, заработался я.
Спасибо.
 
Подскажите, в чем разница этих конструкций.
Всегда ли будут одинаковые результаты?
Второй вариант значительно короче.
Изменено: Viper25 - 12.06.2018 22:54:46
 
Viper25, подозреваю, что на большом массиве первый вариант отработает не критично, но быстрее. Просто потому что SUMX будет высчитывать выражение для каждой строки. я бы использовал в написании первый вариант.
Ну, а если хотите гоняться за краткостью, то у вас же в сводной все равно выводится сумма по полю доход в неявной мере, в таком случае можете создать явную меру:
Код
Доход2:=SUM([Доход])

после чего применить к ней фильтр в форме сокращенной записи вот так:
Код
ДоходФакт3:=[Доход2]('Исходник'[Статус]="Факт")

получается еще короче чем с SUMX  ;)
А вообще по своему опыту не рекомендую использовать в сводной одновременно и явные и не явные меры, ибо как только модель начинает обрастать всяким интересным разобраться потом что к чему крайне не просто. Да и руку набиваешь пока простые меры создаешь - функции не забываешь.
Изменено: PooHkrd - 13.06.2018 09:29:17
Вот горшок пустой, он предмет простой...
 
Цитата
Viper25 написал:
Подскажите, в чем разница этих конструкций.
По идее - никакой. С точки зрения DaxStudio развёртываются в один и тот же SQL запрос

Код
Select Sum('Исходник'[Доход]) From 'Исходник' Where 'Исходник'[Статус] = "Факт";
 
Андрей VG, т.е. на основании разных формул могут генериться одинаковые запросы? О, не знал. Надо будет по-ковыряться в DAX Studio, интересный для отладки инструмент.
Вот горшок пустой, он предмет простой...
 
PooHkrd,
Цитата
PooHkrd написал:
[Доход2]('Исходник'[Статус]="Факт")
вы третий человек на моей памяти, который использует такую нотацию. :) (https://twitter.com/marcorus/status/995016349356347392)
может быть и короче, но не факт, что читабельнее.
F1 творит чудеса
Страницы: 1 2 След.
Наверх