Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1 2 След.
RSS
DAX-POWER_PIVOT - Распределение значений в столбцах или строках сводной таблицы, в зависимости от результата вычислений других столбцов или строк
 
Здравствуйте!
Возник вопрос. Возможно ли с помощью Дах распределять расходы, и заполнять столбцы, строки, в зависимости от результата смежных вычислений:
Например:
Дана общая стоимость производства продукции в кондитерском баре. Количество произведенной продукции по маркам.
Распределение расходов по группам и по статьям, часть расходов (например на сырье), складывают стоимость выпущенных тортов напрямую, а часть расходов расходы, таких как зарплата персонала, ложатся косвенно, сверху, уже после выпуска продукции, учитывая различные, определенные правила.
Помимо этого есть расходы которые могут частично ложиться на продукцию сразу же, а могут распределяться и позже. Например элетроэнергия.
Предположим что кондитерский магазин использует несколько источников энергии, по ряду из них он платит по счетчикам, и может сразу же распределить киловатты и  суммы на каждый вид произведенного изделия, а также ежемесячно оплачивает абон. плату за использование провода линии электропередач управляющей компании.

Возможно ли все это просчитать в Dax PowerPivot, можно ли распределить расходы между продуктами, используя определенный критерий для каждого из показателей? или все это нужно делтать в первичной выборке в PowerQuery иди еще раньше до нее, в первичном файле загрузки?

Очень бы хотелось увидеть решение того, как можно было бы просчитать все это в Dax, я попробовал поковырять меры, по выделял объем производства, но что-то не особо эффективно. Опять моя мера созданная как попытка просчитать средневзевшенное по статьям, показывает нули.
Фаил, максимально приближенный к рабочим данным, а также пояснения в картинке ниже и в самом файле прилагаются. Надеюсь на помощь.
Реальный фаил состоит из примерно 45тысяч строк, с различными детализациями по статьям, с дополнительными уровнями группировок. Но общий смысл я думаю тут улавливается. И его обработка до момента подготовки к выводу в PowerQuery занимает около 40 минут. Очень не хотелось бы ковыряться с первичной.
Изменено: lostandleft - 16 мар 2020 09:42:41
 
Господа, неужели вариантов нет и нужно в PQ все это ваять????
Может быть как-то функцией DATATABLE? Создать новую таблицу из уже имеющихся данных и в ней распределить все согласно критериев?
Ни одного ответа за полтора дня.  :(  
 
lostandleft, мое личное мнение: нужно переделывать всю модель данных. Вы попытались все упихнуть в одну таблицу, что негативно сказывается для проведения расчетов. Самое простое - поле "атрибут" надо разбивать на два отдельных столбца. Убирать пересечения ингридиентов и продукции и разносить по разным таблицам. Сделав правильную модель данных, можно проводить сложные вычисления на DAX. Сейчас это практически не реально сделать - только простые агрегации.
 
lostandleft, видимо опять поставили ограничения на размер файла в 100Кб модель даже в архиве не проходит, поэтому в картинках.
Поддержу Владимира, что Вам нужно что-то делать с моделью, потому что для зарплаты я допустим решил задачу(см. сводную), но даже для этого мне пришлось сделать отдельный справочник(см.схему), чтобы не уходить в "извраты" в формулах, но даже так я сделал 7 мер(см.меры) что-то можно объединить конечно, но так удобнее управлять.
Сводная.JPG (57.42 КБ)
Изменено: StepanWolkoff - 18 мар 2020 13:22:04
 
Пришлось разбить на два сообщения
Меры.JPG (22.95 КБ)
Схема.JPG (30.6 КБ)
 
Цитата
Vladimir Chebykin написал:
Вы попытались все упихнуть в одну таблицу, что негативно сказывается для проведения расчетов. Самое простое - поле "атрибут" надо разбивать на два отдельных столбца.
Все вчера вечером переделал на PQ ждать уже не мог. О производительности можно только заикаться, около 40 минут идет обработка, если повезет то без вылетов.

К сожалению это требование отчетности, нужно с строках показатели (в т.ч. количественные, суммовые, остатки, продажи...в общем веселый винегрет из разных несвязанных данных) в столбцах месяцы.

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

StepanWolkoff,  запакуйте через winRar пожалуйста, также как это сделал я, я с удовольствием ознакомился бы с Вашими мерами. Если это возможно.
 
lostandleft, упакованный получается 147КБ, и вроде можно было какое-то время загружать 300кб, а сейчас ругается, что надо 100КБ
 
Время сделало петлю и вернулось опять к чему начали.
К сожалению решение на  PQ в новых реалиях не реализуемо, вернее реализуемо, но явно неприальным методом. Нужно вернуться к попыткам созхдать меру в DAX. Что-то мне подсказывает, что из 100 тысяч строк с расходами не имеющими пометки конкретного произведенного товара создавать 600 тысяч строк в PQ разнося на 6 позиций, в реальном файле более чем неудобно и затратно по времени.
StepanWolkoff, если исходники мер сохранились пожалуйста опубликуйте хотя бы в части кода. Я что-то не до конца понимаю ход Ваших мыслей в создании мер.
Код
тут исходники семи мер
Изменено: lostandleft - 25 июн 2020 07:56:24
 
Доброе время суток.
lostandleft, вы либо пример выложите с что есть что надо, либо уже перейдите в личную переписку с Степаном.
P. S. Вам никто не мешает в коде одной меры посчитать значение другой меры и, используя, IF, SWITCH, вычислять другую меру.
 
Андрей VG,  Пример в первом посте!!!
Тамже и задание описано, и фотографии приложены.
нужно создать меру, которая распределяет косвенные расходы.
Решение требуется хотябы для распределения расходов по зарплате, например.
Снимок1.JPG (86.99 КБ)
Снимок2.JPG (84.81 КБ)
Изменено: lostandleft - 25 июн 2020 08:55:39
 
Цитата
lostandleft написал:
Пример в первом посте!!!
Это пример? Ну, если для вас это пример, то почему бы вам не помочь человеку с менее сложной зависимостью между данными Группировка элементов сводной таблицы, полученной из Power Pivot?
Я конечно понимаю, что в правилах требования к максимально боевой структуре. Но, я например, портянку разбирать не буду. Может Степан, может кто-то ещё, если есть ВРЕМЯ и ЖЕЛАНИЕ.
Вы бы к сути проблемы - зависимости вычисления одной меры к от значения другой пример бы создали. Я об этом.
 
lostandleft, открою страшный секрет построения модели данных для сложных расчетов: чем проще тем лучше. ;)
Для факторного анализа у меня есть три справочника с календарем, и через них связаны штук 10 разных таблиц фактов - всякие остатки, плановые приходы, расходы, прогнозы расходов, перемещения между складами и всякое такое, более того, регулярно приходится к этому прикручивать что-то еще.
В результате на обновление очень сложных расчетов уходит 2-3 минуты это с учетом загрузки таблиц через PQ. В таблицах фактов минимальная группировка данных, т.е. детализация до самой элементарной операции, таким образом через дриллдаун можно добраться до конкретной проблемы и настучать ей по башке или по зарплате.
Соблюдайте правила подготовки таблиц перед загрузкой в модель, они на сайте МС опубликованы в учебнике по РР.
Это я не хвалюсь, это ответ на ваш вопрос, можно ли что-то сложное посчитать в РР. Ответ да, можно. Я пока не встречал задач в анализе, которые бы не решались этим инструментом.
Но, построение простой модели для сложных расчетов, это сама по себе задача не простая и требует много времени на подготовку. Т.е. вам по сути нужно быть архитектором информационной системы и заранее продумать какие таблицы будут в модели, какими справочниками все это будет связано и также нужно заранее заложить определенный запас прочности под это все для возможности модернизации и добавления функционала. Для обучения этому всему есть огонь-книга. 1к за такое - это считай что бесплатно. Решать такую задачу на бесплатном форуме не обладая знаниями из такого пособия, мягко говоря не просто.
Изменено: PooHkrd - 25 июн 2020 09:28:41
Вот горшок пустой, он предмет простой...
 
Андрей VG,
Андрей, человек в первом посте говорит про яблоки груши и ягоды, а пример приводит с каким-то промышленным оборудованием. От чего не до конца понятно что он хочет сделать. Лично мне не до конца понятно.
PooHkrd,  Данные уже собраны и готовы для работы, и работают, и уже отчеты построены, перестраивать модель данных идея хорошая. но у меня нет 3х недель для того, чтобы опять все начинать разрабатывать с нуля.
Нужно работать с тем что есть.

В моем примере, нужно распределить расходы, которые не имеют прямого отношения к конкретной произведенной продукции явно. Такие как оплата труда, пропорционально выпуска продукции.
на картинках именно это и показано. Зарплата персонала, явно не распределенная на каждый вид выпускаемой продукции, и указанная в столбике Пусто, должна быть распределена на ПирогВишня, ПирогЕжевика, ПирогКлюква
Изменено: lostandleft - 25 июн 2020 09:30:20
 
Цитата
lostandleft написал:
но у меня нет 3х недель для того, чтобы опять все начинать разрабатывать с нуля.Нужно работать с тем что есть.
Т.е. вы считаете что у помогающих есть вагон времени на разбор вашей модели, а у вас нет времени, на то чтобы её оптимизировать? Ну, дело ваше. Удачи в поиске помощи и обучении.
З.Ы. Иногда реально выгоднее "целый день потерять, потом за 5 минут долететь".
Вот горшок пустой, он предмет простой...
 
PooHkrd,тут, я думаю что речь о неделях.
И данные более чем обыденные. Все это выгружается из 1с каждый день, каждый аналитик, каждый экономист, каждый бухгалтер видит эти данные сформированные в карточках счета и карточках субконто вылюнутые из 1с.
 
Цитата
lostandleft написал:
каждый аналитик, каждый экономист, каждый бухгалтер
Тогда пожелаю вам удачи дождаться ответа этих товарищей, которые видят и готовы уделить время на решение вашей задачи.
 
Андрей VG, спасибо :sceptic:  
 
Цитата
PooHkrd написал:
Но, построение простой модели для сложных расчетов, это сама по себе задача не простая и требует много времени на подготовку. Т.е. вам по сути нужно быть архитектором информационной системы
- ключевое для данного топика. Я об этом же сразу написал, но я так формулировать мысли не умею)). lostandleft, просто я, Алексей, Андрей, Степан имеем чуть-больше опыта и сразу видим, что Ваши запросы (задачи) тривиальны в принципе для DAX, но при условии правильно выстроенной модели данных. А при той структуре, что есть у Вас, эти довольно простые задачи превращаются в большой "гемор" и Степан Вам это продемонстрировал примером решения Выше. Лучше не откладывать и начать изучать теорию и делать уже по канонам - в конечном итоге сэкономите в разы больше времени, потому что будут следующие задачи и они все будут геморройные в плане решения. А по поводу
Цитата
lostandleft написал:
у меня нет 3х недель
, если бы потихоньку параллельно начали делать новую модель данных от времени, когда топик был открыт, то уже бы все сделали по уму как надо и сегодня бы таких вопросов не возникало.
П.С. я помню с прошлого раза, насколько сложно в вашей текущей структуре производить нужные расчеты, поэтому за практическое решение даже браться не буду - толку нет от этого, кроме затыкания брешей на вашем корабле! Это как в Ералаше: "Саня, научи меня плохому! - Отстань от меня, не буду!"
 
Цитата
Vladimir Chebykin написал:
"Саня, научи меня плохому! - Отстань от меня, не буду!"
пошел гуглить
 
lostandleft, открою большой секрет: разнесение зарплаты и прочее разнесение без изменения модели данных в вашем случае невозможно. Например, я создал меру, которая считает долю ГП для разнесения зарплаты для каждого товара, помещенного в столбцы (т.е. "Произведено")


Но, так как у вас нет в исходных данных строк, которые соответствовали бы сочетанию "Произведено"="Пирог Вишня" и "Группировка"="Зарплата", то как бы я не старался, моя мера не будет рассчитываться для вашей сводной в ячейках сводной на пересечении пирожков с вишней и зарплатных строк. До тех пор, пока хотя бы некоторые показатели (например, то самое "Произведено") не будет вынесено в отдельный справочник, как вам показал StepanWolkoff в сообщении #5.

Причина очень простая - когда сводная строится на столбцах из одной таблицы, включается правило AUTOEXISTS - любые расчеты ведутся только для существующих сочетаний, а для несуществующих сочетаний не делается ничего. Если столбцы из разных таблиц - такое правило не работает.

Так что меняйте модель данных. Для ваших двух статей, которые нужно разнести, достаточно будет сделать модель как в сообщении #5.
мера была такая, но использовать ее бессмысленно
F1 творит чудеса
 
Господа, нет ничего проще, чем создать справочник произведенной продукции и состыковать его к столбцу Произведено.
Бог с ним, это вообще не проблема.
Хорошо, согласен с Вашими замечаниями.
Также я разнес столбики Количество и Сумма отдельно. Но что-то это не особо помогло в решении головоломки.

Максим Зеленский, К сожалению мера не работает, какие бы справочники я не формировал.
Обновленный пример ниже. Во вложении.
Изменено: lostandleft - 25 июн 2020 12:32:09
 
Цитата
lostandleft написал:
К сожалению мера не работает, какие бы справочники я не формировал.
Цитата
Максим Зеленский написал:
мера была такая, но использовать ее бессмысленно
F1 творит чудеса
 
Ох. каким же образом в 4 сообщение было найдено решение  :qstn:  
 
Цитата
Максим Зеленский написал:
До тех пор, пока хотя бы некоторые показатели (например, то самое "Произведено") не будет вынесено в отдельный справочник
В отдельный справочник вынес, это не решило проблему.
И справочник создал, и столбики отдельные сделал под сумму и под количество. Это не решило проблему, к сожалению.
похоже что есть один человек знает как ее решить, но к сожалению он не посещал форум с мая :)
Изменено: lostandleft - 25 июн 2020 12:57:37
 
Код
Сумма по сумме   :=SUM('Таблица1'[Сумма])

ПроизведеноТМЦ   :=VAR _Product = IF(HASONEVALUE('Произведено'[Произведено]),VALUES('Произведено'[Произведено]))
RETURN 
CALCULATE(
sum([Количество]),
'Таблица1'[Группировка]="Готовая продукция",ALL('Таблица1'[Движение],'Таблица1'[Показатель]),
'Таблица1'[Детали]=_Product,ALL('Произведено'[Произведено])
)
мера 1   :=VAR _Group = CALCULATE(IF(HASONEVALUE('Таблица1'[Группировка]),VALUES('Таблица1'[Группировка])),ALL('Произведено')) RETURN IF(_Group="Зарплата",CALCULATE([Сумма по сумме],ALL('Произведено'))*[Доля],[Сумма по сумме])

Немного не дожали. Остальные меры ваши
F1 творит чудеса
 
Максим Зеленский,
Максим Спасибо!
В  который раз выручаете!

Подскажите еще. Нет ли способа не привязываться вручную к указанию IF группа =
Нет ли возможности по умочанию считать меру если в столбце [произведено] значения имеют тип IsBlank, чтобы вручную не привязываться к значению "Зарплата" или "Амортизация" или еще что-то, во первых какая-то зарплата может быть частично разнесена в деталях на продукт напрямую, и колонка [произведено] , будет заполнена, а, во вторых группы могут появляться в бесконечно больших количествах и все их постоянно держать в памяти не получится

Попробовал вот такой вариант получается полная ерунда
Код
=VAR _group =
CALCULATE(
     IF(
           HASONEVALUE('Таблица1'[Произведено]);
           (BLANK())
       );
     ALL('Произведено')
)
RETURN 
if(ISBLANK(_group)

;CALCULATE([Сумма по столбцу Сумма];ALL('Произведено'))*[Доля]

;[Сумма по столбцу Сумма]
)
Изменено: lostandleft - 25 июн 2020 15:39:59
 
Итак господа, еще раз спасибо всем кто попытался помочь!
Тем не менее, я понял, что ничего сделать не получится в даксе. Он просто для этого не заточен.
Какую еще модель данных нужно сделать я реально не понимаю.
Есть глобальная себестоимость, которая формируется как из прямых так и из косвенных затрат. Косвенные затраты, коих очень много ложатся на продукты согласно определенных критериев, в общем случае, и чаще всего по объему выпуска продукции.

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

Либо у меня не хватает головных усилий и серого вещества  на это дело.

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

Итоговая мера которая получилась, и которая считает вроде бы все нормально вот такая, но это до поры до времени, пока не свернешь группу, в развернутом виде все отлично, в свернутом, не суммирует посчитанные распределенные значения и те. которые заданы изначально:
Код
=VAR _group ='Таблица1'[Сумма по столбцу Сумма]*'Таблица1'[Доля]

RETURN 
if(ISBLANK(_group)

;CALCULATE([Сумма по столбцу Сумма];ALL('Произведено'))*[Доля]
;[Сумма по столбцу Сумма]

)

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

Опять все вернулось на круги своя, идем в тормознутый PQ, ждем часовые обработки  :evil:  
Изменено: lostandleft - 25 июн 2020 18:13:22
 
Уважаемый lostandleft,
Цитата
lostandleft написал:
я понял, ничего сделать не получится в даксе. Он просто для этого не заточен.
Вы просто не умеете или бросаете на полдороги. По крайней мере, я не вижу в этой задаче ничего такого, что DAX не смог бы решить.
Цитата
lostandleft написал:
Косвенные затраты, коих очень много ложатся на продукты согласно определенных критериев, в общем случае, и чаще всего по объему выпуска продукции.
Чудес не бывает. Если вам говорят иначе, значит, вам нагло врут. В данном случае к чудесам относится возможность текущего уровня технологий человечества угадать, по какому же принципу вы хотите распределить затраты заранее неизвестной группы затрат. В начальном сообщении вы говорили о том, что затраты на электроэнергию распределяются пропорционально одному показателю, а затраты на зарплату пропорционально другому. Либо вы напрягаетесь и говорите системе, какие затраты по какому принципу ей нужно разносить (хотя бы утруждаете себя проставить тип разнесения у строки затрат в исходных данных), либо делаете разнесение по одному критерию (хотя бы тот же объем выпуска), либо остаетесь без разнесения. Так что хотя бы принцип определения доли вам придется додумывать
Код
=
CALCULATE ( [Сумма по столбцу Сумма], 'Произведено'[Произведено] = BLANK () ) * [Доля] + [Сумма по столбцу Сумма]

Сработает даже если у вас в одной строке сводной будет и разнесенная, и не разнесенная сумма. Например, часть зарплаты бармена разнесена на пироги, а часть общей суммой. Для итогов аналогично.

PS У вас неплохой потенциал и думаете в правильном направлении, но вы рано бросаете.  
Изменено: Максим Зеленский - 25 июн 2020 18:42:40 (упростил формулу)
F1 творит чудеса
 
Максим Зеленский,
Максим ну как же на пол дороги, я же целый день сегодня комбинации перебираю, весь рабочий день превратился в работу с примером. :(

Вы совершенно правы что типы разнесения накладных расходов могут быть разными!!!
Чаще всего 1с и бухгалтер разносят все по обороту.
Однако бывают и другие возможности и варинты!
И это более правильно было бы, но я сдулся!
Изначально мне показалось что сделать несколько мер, как раз и описывающих эти варианты было бы более правильно чем писать тонны кода на PQ.
Однако нырнув, и поняв что я не выгребу включил то, что я уже казалось бы хоть чуть-чуть освоил.
Задачу тогда я решил, но база данных растет, растет постоянно, сейчас задача похожая, и уже почти под 100 тысяч строк записей, делать из них 600 тысяч строк записей очень не разумно...подумал я. И решил вернуться к рассмотрению через меры. Плюс начал немного осваивать Bi.
Мне кстати показалось что PQ в нем работает быстрее! Запросы как-то ощутимо быстрее в предпросмотре загружались.
решил в Bi сделать отчет, как раз для него и понадобилась опять эта самая мера, для детализации себестоимости определенных продуктов.
 
Максим Зеленский,
Мера работает, при объединении, Спасибо!
Осталось опять убрать ненужное значение пустого столбика (без указания выпущенной продукции) и можно пробовать то что получилось на живом проекте.

От  куда у Вас только столько терпения, спасибо еще раз!
Страницы: 1 2 След.
Читают тему (гостей: 1)
Наверх