Здравствуйте! Возник вопрос. Возможно ли с помощью Дах распределять расходы, и заполнять столбцы, строки, в зависимости от результата смежных вычислений: Например: Дана общая стоимость производства продукции в кондитерском баре. Количество произведенной продукции по маркам. Распределение расходов по группам и по статьям, часть расходов (например на сырье), складывают стоимость выпущенных тортов напрямую, а часть расходов расходы, таких как зарплата персонала, ложатся косвенно, сверху, уже после выпуска продукции, учитывая различные, определенные правила. Помимо этого есть расходы которые могут частично ложиться на продукцию сразу же, а могут распределяться и позже. Например элетроэнергия. Предположим что кондитерский магазин использует несколько источников энергии, по ряду из них он платит по счетчикам, и может сразу же распределить киловатты и суммы на каждый вид произведенного изделия, а также ежемесячно оплачивает абон. плату за использование провода линии электропередач управляющей компании.
Возможно ли все это просчитать в Dax PowerPivot, можно ли распределить расходы между продуктами, используя определенный критерий для каждого из показателей? или все это нужно делтать в первичной выборке в PowerQuery иди еще раньше до нее, в первичном файле загрузки?
Очень бы хотелось увидеть решение того, как можно было бы просчитать все это в Dax, я попробовал поковырять меры, по выделял объем производства, но что-то не особо эффективно. Опять моя мера созданная как попытка просчитать средневзевшенное по статьям, показывает нули. Фаил, максимально приближенный к рабочим данным, а также пояснения в картинке ниже и в самом файле прилагаются. Надеюсь на помощь. Реальный фаил состоит из примерно 45тысяч строк, с различными детализациями по статьям, с дополнительными уровнями группировок. Но общий смысл я думаю тут улавливается. И его обработка до момента подготовки к выводу в PowerQuery занимает около 40 минут. Очень не хотелось бы ковыряться с первичной.
Господа, неужели вариантов нет и нужно в PQ все это ваять???? Может быть как-то функцией DATATABLE? Создать новую таблицу из уже имеющихся данных и в ней распределить все согласно критериев? Ни одного ответа за полтора дня.
lostandleft, мое личное мнение: нужно переделывать всю модель данных. Вы попытались все упихнуть в одну таблицу, что негативно сказывается для проведения расчетов. Самое простое - поле "атрибут" надо разбивать на два отдельных столбца. Убирать пересечения ингридиентов и продукции и разносить по разным таблицам. Сделав правильную модель данных, можно проводить сложные вычисления на DAX. Сейчас это практически не реально сделать - только простые агрегации.
lostandleft, видимо опять поставили ограничения на размер файла в 100Кб модель даже в архиве не проходит, поэтому в картинках. Поддержу Владимира, что Вам нужно что-то делать с моделью, потому что для зарплаты я допустим решил задачу(см. сводную), но даже для этого мне пришлось сделать отдельный справочник(см.схему), чтобы не уходить в "извраты" в формулах, но даже так я сделал 7 мер(см.меры) что-то можно объединить конечно, но так удобнее управлять.
Vladimir Chebykin написал: Вы попытались все упихнуть в одну таблицу, что негативно сказывается для проведения расчетов. Самое простое - поле "атрибут" надо разбивать на два отдельных столбца.
Все вчера вечером переделал на PQ ждать уже не мог. О производительности можно только заикаться, около 40 минут идет обработка, если повезет то без вылетов.
К сожалению это требование отчетности, нужно с строках показатели (в т.ч. количественные, суммовые, остатки, продажи...в общем веселый винегрет из разных несвязанных данных) в столбцах месяцы.
Как в сводных таблицах получить все в строках, без уровней через модель данных не представляю себе совсем.
StepanWolkoff, запакуйте через winRar пожалуйста, также как это сделал я, я с удовольствием ознакомился бы с Вашими мерами. Если это возможно.
Время сделало петлю и вернулось опять к чему начали. К сожалению решение на PQ в новых реалиях не реализуемо, вернее реализуемо, но явно неприальным методом. Нужно вернуться к попыткам созхдать меру в DAX. Что-то мне подсказывает, что из 100 тысяч строк с расходами не имеющими пометки конкретного произведенного товара создавать 600 тысяч строк в PQ разнося на 6 позиций, в реальном файле более чем неудобно и затратно по времени. StepanWolkoff, если исходники мер сохранились пожалуйста опубликуйте хотя бы в части кода. Я что-то не до конца понимаю ход Ваших мыслей в создании мер.
Доброе время суток. lostandleft, вы либо пример выложите с что есть что надо, либо уже перейдите в личную переписку с Степаном. P. S. Вам никто не мешает в коде одной меры посчитать значение другой меры и, используя, IF, SWITCH, вычислять другую меру.
Андрей VG, Пример в первом посте!!! Тамже и задание описано, и фотографии приложены. нужно создать меру, которая распределяет косвенные расходы. Решение требуется хотябы для распределения расходов по зарплате, например.
Это пример? Ну, если для вас это пример, то почему бы вам не помочь человеку с менее сложной зависимостью между данными Группировка элементов сводной таблицы, полученной из Power Pivot? Я конечно понимаю, что в правилах требования к максимально боевой структуре. Но, я например, портянку разбирать не буду. Может Степан, может кто-то ещё, если есть ВРЕМЯ и ЖЕЛАНИЕ. Вы бы к сути проблемы - зависимости вычисления одной меры к от значения другой пример бы создали. Я об этом.
lostandleft, открою страшный секрет построения модели данных для сложных расчетов: чем проще тем лучше. Для факторного анализа у меня есть три справочника с календарем, и через них связаны штук 10 разных таблиц фактов - всякие остатки, плановые приходы, расходы, прогнозы расходов, перемещения между складами и всякое такое, более того, регулярно приходится к этому прикручивать что-то еще. В результате на обновление очень сложных расчетов уходит 2-3 минуты это с учетом загрузки таблиц через PQ. В таблицах фактов минимальная группировка данных, т.е. детализация до самой элементарной операции, таким образом через дриллдаун можно добраться до конкретной проблемы и настучать ей по башке или по зарплате. Соблюдайте правила подготовки таблиц перед загрузкой в модель, они на сайте МС опубликованы в учебнике по РР. Это я не хвалюсь, это ответ на ваш вопрос, можно ли что-то сложное посчитать в РР. Ответ да, можно. Я пока не встречал задач в анализе, которые бы не решались этим инструментом. Но, построение простой модели для сложных расчетов, это сама по себе задача не простая и требует много времени на подготовку. Т.е. вам по сути нужно быть архитектором информационной системы и заранее продумать какие таблицы будут в модели, какими справочниками все это будет связано и также нужно заранее заложить определенный запас прочности под это все для возможности модернизации и добавления функционала. Для обучения этому всему есть огонь-книга. 1к за такое - это считай что бесплатно. Решать такую задачу на бесплатном форуме не обладая знаниями из такого пособия, мягко говоря не просто.
Андрей VG, Андрей, человек в первом посте говорит про яблоки груши и ягоды, а пример приводит с каким-то промышленным оборудованием. От чего не до конца понятно что он хочет сделать. Лично мне не до конца понятно. PooHkrd, Данные уже собраны и готовы для работы, и работают, и уже отчеты построены, перестраивать модель данных идея хорошая. но у меня нет 3х недель для того, чтобы опять все начинать разрабатывать с нуля. Нужно работать с тем что есть.
В моем примере, нужно распределить расходы, которые не имеют прямого отношения к конкретной произведенной продукции явно. Такие как оплата труда, пропорционально выпуска продукции. на картинках именно это и показано. Зарплата персонала, явно не распределенная на каждый вид выпускаемой продукции, и указанная в столбике Пусто, должна быть распределена на ПирогВишня, ПирогЕжевика, ПирогКлюква
lostandleft написал: но у меня нет 3х недель для того, чтобы опять все начинать разрабатывать с нуля.Нужно работать с тем что есть.
Т.е. вы считаете что у помогающих есть вагон времени на разбор вашей модели, а у вас нет времени, на то чтобы её оптимизировать? Ну, дело ваше. Удачи в поиске помощи и обучении. З.Ы. Иногда реально выгоднее "целый день потерять, потом за 5 минут долететь".
PooHkrd,тут, я думаю что речь о неделях. И данные более чем обыденные. Все это выгружается из 1с каждый день, каждый аналитик, каждый экономист, каждый бухгалтер видит эти данные сформированные в карточках счета и карточках субконто вылюнутые из 1с.
PooHkrd написал: Но, построение простой модели для сложных расчетов, это сама по себе задача не простая и требует много времени на подготовку. Т.е. вам по сути нужно быть архитектором информационной системы
- ключевое для данного топика. Я об этом же сразу написал, но я так формулировать мысли не умею)). lostandleft, просто я, Алексей, Андрей, Степан имеем чуть-больше опыта и сразу видим, что Ваши запросы (задачи) тривиальны в принципе для DAX, но при условии правильно выстроенной модели данных. А при той структуре, что есть у Вас, эти довольно простые задачи превращаются в большой "гемор" и Степан Вам это продемонстрировал примером решения Выше. Лучше не откладывать и начать изучать теорию и делать уже по канонам - в конечном итоге сэкономите в разы больше времени, потому что будут следующие задачи и они все будут геморройные в плане решения. А по поводу
, если бы потихоньку параллельно начали делать новую модель данных от времени, когда топик был открыт, то уже бы все сделали по уму как надо и сегодня бы таких вопросов не возникало. П.С. я помню с прошлого раза, насколько сложно в вашей текущей структуре производить нужные расчеты, поэтому за практическое решение даже браться не буду - толку нет от этого, кроме затыкания брешей на вашем корабле! Это как в Ералаше: "Саня, научи меня плохому! - Отстань от меня, не буду!"
lostandleft, открою большой секрет: разнесение зарплаты и прочее разнесение без изменения модели данных в вашем случае невозможно. Например, я создал меру, которая считает долю ГП для разнесения зарплаты для каждого товара, помещенного в столбцы (т.е. "Произведено")
Но, так как у вас нет в исходных данных строк, которые соответствовали бы сочетанию "Произведено"="Пирог Вишня" и "Группировка"="Зарплата", то как бы я не старался, моя мера не будет рассчитываться для вашей сводной в ячейках сводной на пересечении пирожков с вишней и зарплатных строк. До тех пор, пока хотя бы некоторые показатели (например, то самое "Произведено") не будет вынесено в отдельный справочник, как вам показал StepanWolkoff в сообщении #5.
Причина очень простая - когда сводная строится на столбцах из одной таблицы, включается правило AUTOEXISTS - любые расчеты ведутся только для существующих сочетаний, а для несуществующих сочетаний не делается ничего. Если столбцы из разных таблиц - такое правило не работает.
Так что меняйте модель данных. Для ваших двух статей, которые нужно разнести, достаточно будет сделать модель как в сообщении #5.
Господа, нет ничего проще, чем создать справочник произведенной продукции и состыковать его к столбцу Произведено. Бог с ним, это вообще не проблема. Хорошо, согласен с Вашими замечаниями. Также я разнес столбики Количество и Сумма отдельно. Но что-то это не особо помогло в решении головоломки.
Максим Зеленский, К сожалению мера не работает, какие бы справочники я не формировал. Обновленный пример ниже. Во вложении.
Максим Зеленский написал: До тех пор, пока хотя бы некоторые показатели (например, то самое "Произведено") не будет вынесено в отдельный справочник
В отдельный справочник вынес, это не решило проблему. И справочник создал, и столбики отдельные сделал под сумму и под количество. Это не решило проблему, к сожалению. похоже что есть один человек знает как ее решить, но к сожалению он не посещал форум с мая
Подскажите еще. Нет ли способа не привязываться вручную к указанию IF группа = Нет ли возможности по умочанию считать меру если в столбце [произведено] значения имеют тип IsBlank, чтобы вручную не привязываться к значению "Зарплата" или "Амортизация" или еще что-то, во первых какая-то зарплата может быть частично разнесена в деталях на продукт напрямую, и колонка [произведено] , будет заполнена, а, во вторых группы могут появляться в бесконечно больших количествах и все их постоянно держать в памяти не получится
Попробовал вот такой вариант получается полная ерунда
Итак господа, еще раз спасибо всем кто попытался помочь! Тем не менее, я понял, что ничего сделать не получится в даксе. Он просто для этого не заточен. Какую еще модель данных нужно сделать я реально не понимаю. Есть глобальная себестоимость, которая формируется как из прямых так и из косвенных затрат. Косвенные затраты, коих очень много ложатся на продукты согласно определенных критериев, в общем случае, и чаще всего по объему выпуска продукции.
1с выплевывает карточку счета всю навалом, и прямые и косвенные расходы. Все что можно от туда поймать это итоговые сформированные значения выпуска продукции. Образно говоря на входе в 1с, сахар, петрушка, ванилин, малина и мука, на выходе пирог особый+пирог обычный. Задачу увидеть распределение накладных расходов через меру, и проверку тем самым закрытия 1с получается, что решить не получается.
Либо у меня не хватает головных усилий и серого вещества на это дело.
Завтра все сделаю на PQ, благо этот инструмент понятен в использовании, и относительно прост в изучении. По крайней меря для меня, уж очень как-то трудно мне даются эти меры.
Итоговая мера которая получилась, и которая считает вроде бы все нормально вот такая, но это до поры до времени, пока не свернешь группу, в развернутом виде все отлично, в свернутом, не суммирует посчитанные распределенные значения и те. которые заданы изначально:
Код
=VAR _group ='Таблица1'[Сумма по столбцу Сумма]*'Таблица1'[Доля]
RETURN
if(ISBLANK(_group)
;CALCULATE([Сумма по столбцу Сумма];ALL('Произведено'))*[Доля]
;[Сумма по столбцу Сумма]
)
Как я теперь понимаю обозревателей выше, все было бесперспективно с самого начала, если в одной группе будет и необходимость распределения косвенных затрат, и будут и прямые затраты, например группа электроэнергия в этой мере считается не правильно. При группировке получается полная ерунда.
Опять все вернулось на круги своя, идем в тормознутый PQ, ждем часовые обработки
lostandleft написал: я понял, ничего сделать не получится в даксе. Он просто для этого не заточен.
Вы просто не умеете или бросаете на полдороги. По крайней мере, я не вижу в этой задаче ничего такого, что DAX не смог бы решить.
Цитата
lostandleft написал: Косвенные затраты, коих очень много ложатся на продукты согласно определенных критериев, в общем случае, и чаще всего по объему выпуска продукции.
Чудес не бывает. Если вам говорят иначе, значит, вам нагло врут. В данном случае к чудесам относится возможность текущего уровня технологий человечества угадать, по какому же принципу вы хотите распределить затраты заранее неизвестной группы затрат. В начальном сообщении вы говорили о том, что затраты на электроэнергию распределяются пропорционально одному показателю, а затраты на зарплату пропорционально другому. Либо вы напрягаетесь и говорите системе, какие затраты по какому принципу ей нужно разносить (хотя бы утруждаете себя проставить тип разнесения у строки затрат в исходных данных), либо делаете разнесение по одному критерию (хотя бы тот же объем выпуска), либо остаетесь без разнесения. Так что хотя бы принцип определения доли вам придется додумывать
Код
=
CALCULATE ( [Сумма по столбцу Сумма], 'Произведено'[Произведено] = BLANK () ) * [Доля] + [Сумма по столбцу Сумма]
Сработает даже если у вас в одной строке сводной будет и разнесенная, и не разнесенная сумма. Например, часть зарплаты бармена разнесена на пироги, а часть общей суммой. Для итогов аналогично.
PS У вас неплохой потенциал и думаете в правильном направлении, но вы рано бросаете.
Максим Зеленский, Максим ну как же на пол дороги, я же целый день сегодня комбинации перебираю, весь рабочий день превратился в работу с примером.
Вы совершенно правы что типы разнесения накладных расходов могут быть разными!!! Чаще всего 1с и бухгалтер разносят все по обороту. Однако бывают и другие возможности и варинты! И это более правильно было бы, но я сдулся! Изначально мне показалось что сделать несколько мер, как раз и описывающих эти варианты было бы более правильно чем писать тонны кода на PQ. Однако нырнув, и поняв что я не выгребу включил то, что я уже казалось бы хоть чуть-чуть освоил. Задачу тогда я решил, но база данных растет, растет постоянно, сейчас задача похожая, и уже почти под 100 тысяч строк записей, делать из них 600 тысяч строк записей очень не разумно...подумал я. И решил вернуться к рассмотрению через меры. Плюс начал немного осваивать Bi. Мне кстати показалось что PQ в нем работает быстрее! Запросы как-то ощутимо быстрее в предпросмотре загружались. решил в Bi сделать отчет, как раз для него и понадобилась опять эта самая мера, для детализации себестоимости определенных продуктов.
Максим Зеленский, Мера работает, при объединении, Спасибо! Осталось опять убрать ненужное значение пустого столбика (без указания выпущенной продукции) и можно пробовать то что получилось на живом проекте.
От куда у Вас только столько терпения, спасибо еще раз!