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

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

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

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

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

получается еще короче чем с SUMX  ;)
А вообще по своему опыту не рекомендую использовать в сводной одновременно и явные и не явные меры, ибо как только модель начинает обрастать всяким интересным разобраться потом что к чему крайне не просто. Да и руку набиваешь пока простые меры создаешь - функции не забываешь.
Изменено: PooHkrd - 13 Июн 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 След.
Читают тему (гостей: 1)
Наверх