Пытался найти ответ на свой вопрос, к сожалению не нашел или не понял как применить, вся надежда на вашу помощь. У меня есть расчет с себестоимости, который необходимо сделать динамическим через сводные таблицу с применением PowerPivot и Power Query, рассчитывать следующие столбцы:
Скрытый текст
Рент-сть в цене реализации в %
Маржинальная прибыль на 1 а/м (руб)
Маржинальная прибыль на отгрузку (тыс.руб.)
Маржинальная рент-ть в цене реализации (%)
В расчете я применил свёртывание столбцов для расчета мер на выпуск и на ед. продукции, к сожалению, не знаю как доработать меры для вычисления в этой сводной таблице выше перечисленные столбцы, чтобы он правильно рассчитывал значения и итоги в зависимости от выбранных полей (тип набора данных, продукт, семейство и т.д. ). Пробовал писать формулы в PowerQuery, но к сожалению он не правильно считает итоги, и вместо того чтобы пересчитывать складывает проценты. Заранее огромное спасибо!
Alexsandr B, вы же понимаете что составить что-то похожее на меру не видя структуры источника мягко говоря затруднительно? Пример структуры вашей модели данных покажете? И не на картинке, а в файлике. И неплохо бы итоговый результат для контроля расчетов показать, что вы хотите получить на выходе.
Не получилось сразу выложить, не смог сжать до 100 кб, выложил на яндекс диск https://yadi.sk/i/OfedH9zYU0QMwQ Проверку можно сделать по следующему показателю: Рент-сть в цене реализации в % = ("Отгруж без бонусов / Средн цена продажи без бонусов (руб)" - "Полная себестоимость")/ "Отгруж без бонусов / Средн цена продажи без бонусов (руб)"
В файле выделены и в данных и в сводной темно серым цветом, в источнике прописаны формулы (как должно считаться по позициям).
Сейчас, к сожалению времени мало. Но вам для вывода нужных расчетов исходя из значения столбца "Атрибут" нужно использовать функцию SWITCH. Поищите по форуму - тут есть не мало примеров её применения.
в столбце "Рент-сть в цене реализации в %" применять формулу CALCULATE(Sum('tDAT'[Значение]);'tDAT'[Атрибут]="Полная себестоимость") которая должна брать суммарные данные (в зависимости от выбранных полей и фильтров сводной таблицы) из перевернутого столбца полная себестоимость (в тек. таблице название столбца атрибут), в дальнейшем нужно будет с помощью такой же формулы подтянуть данные по продажам чтобы вычислить рентабельность и др. показатели. Что-то подобное можно сделать через формулу суммеслилимн().
для всех остальных столбцов применять CALCULATE(SUM('tDAT'[Значение]);'tDAT'[На выпуск / на единицу]="на выпуск") (эта часть работает)
Полная формула: Копия на выпуск:=SWITCH(VALUES(tDAT[Атрибут]);"Рент-сть в цене реализации в %";CALCULATE(Sum('tDAT'[Значение]);'tDAT'[Атрибут]="Полная себестоимость");CALCULATE(SUM('tDAT'[Значение]);'tDAT'[На выпуск / на единицу]="на выпуск"))
К сожалению при добавлении меры в сводную таблицы выдает ошибку "...ожидалось единственное значение, однако передана таблица или несколько значений …. " Что-то делаю не так, а понять что не могу.
Alexsandr B, это вам ругаются итоги сводной, т.к. VALUES(tDAT[Атрибут]) в итогах дают более одного значения. Здесь рекомендую использовать конструкцию вида
Код
Мера:=IF(ISFILTERED(tDAT[Атрибут]); SWITCH(ваша мера); формула для расчета итогов )
к сожалению не работает, рентабельность в цене реализации не рассчитывает. Думаю как передать в Swich текущую строку столбца Атрибут, по ней уже чтобы он применял нужную формулу расчета значений к столбцу. Но как это сделать не пойму, с формулами ексель это сделать легко, а тут загадка (((
Конечно оно не считается, в контексте заданных фильтров сводной таблицей и вами в функции CALCULATE нет строк для расчетов. В вашей ситуации нужно убирать контекст, который задает сводная по столбцу атрибут при помощи функции ALL, а уже после этого, фильтроваться по другим значениям строк, и уже в этой таблице считать сумму. Посмотрите пример.
Alexsandr B, и еще обратите внимание, что в последнем примере я подправил ваш запрос, который закидывает таблицу в модель - убрал из названий статусов разрывы строк. С разрывами не всегда корректно производилось сравнение в мерах.