Рвав-рвав, собака Смайл снова взялся за перо!
Сегодня замечательный собака Смайл расскажет вам о том, как построить практически любой отчет табличного вида, да-да, и это не шутка!
Способ есть, он довольно муторный, но рабочий)
Не секрет, что таблица (или матрица), используется практически повсеместно. Но часто построение таблиц "как есть" не устраивает конечных пользователей, и обычно требуется осуществление дополнительных расчетов. Power BI, в отличие от Excel, не может напрямую работать с ячейкой, он работает только со столбцом, и поэтому требование конечного пользователя "собрать кучу различных показателей в одной форме" выполнить весьма затруднительно. То есть, замечательная последовательность: "Берем это и это, складываем, минусуем вон то, а полученный результат умножаем на такой-то коэффициент" вполне может вам встретиться в реальной жизни :-)
А в качестве вашего "добивания" будет: "Хочу таблицу именно в таком виде, поскольку формы нашей отчетности (та-дам!) утверждены".
В Петербург пришла осень, мокро и холодно, однако грустный собака решил в очередной раз улучшить свою карму, и поделиться знаниями.
Итак, сейчас посмотрим, как описанное выше выглядит на практике.
- Таблица "Распределение средств" содержит необходимые сведения об операции, а именно, дату операции, соответствующую статью, сумму и направление транзакции:
- В качестве отдельной таблицы выделен календарь.
- Также для удобства создана отдельная таблица для хранения мер, хоть теперь и можно делать группировки сущностей по папочкам внутри таблицы.
Задача:
На основе представленных данных построить элемент табличного вида, строки которого расположены в следующем порядке:
- На 1-м месте расположены статьи доходов.
- На 2-м месте расположены статьи расходов.
- На 3-м месте выведен расчетный показатель "Маржа".
- На 4-м месте выведен расчетный показатель "Маржинальность".
Как вы понимаете, при разработке какого-либо отчета есть 2 пути: можно либо выстраивать расчеты на основании расчетных столбцов, либо пользоваться мерами. Естественно, оба пути можно совмещать.
Каждый вариант имеет свои плюсы и минусы. Например, при использовании мер, в отличии от расчетного столбца, возможность "drill down" до исходной операции в строке таблицы пропадает. Увы, но это так работает. Зато часть расчетов, которые возможно сделать при помощи мер, реализовать с использованием расчетных столбцов или тяжело, или невозможно. Таким примером может служить расчет накопительных итогов в течении длительного периода времени.
Рвав-рвав, применительно к обозначенной задаче, можно, конечно, иди и 1-м путем, однако, основываясь на опыте хозяина, как-то построившего подобный отчет при помощи расчетных столбцов, собака Смайл предлагает использовать меры, и необходимо заранее продумать требования, поступившие от клиента, чтобы выстроить в голове правильную структуру отчета.
Процесс разработки:
- Создана таблица "Справочник показателей", содержащая список строк будущего отчета, и их порядок:
Рвав-рвав, предупреждаю сразу: поскольку структура жесткая, номера, в реальности, можно присваивать не по порядку, а с запасом, то есть “10”, “20”, и так далее, дабы потом, когда вас попросят что-то добавить, не было мучительно больно, и не пришлось переделывать все. Это я вам как опытный собакен говорю, хоть собаку (хе-хе), на этом и не ел!
После того, как последовательность строк отчета была выстроена, можно приступать к расчету необходимых величин. Признак того, к какой группе у нас относится статья (то есть порядок группировки статей), в нашем примере присутствует, в реальности вам нужно будет узнать условия и порядок группировки у постановщика задачи. При этом следует учитывать, что, скорее всего, может быть несколько уровней вложенности, то есть составляющие общего итога могут сами состоять из чего-нибудь еще.
- В нашем случае у нас будет две составные статьи, которые, одновременно, будут являться и конечным итогом. Для определения итога по доходам можно написать меру следующего вида:
Показатель Доходы =
CALCULATE (
SUM ( 'Распределение средств'[Сумма] ),
'Распределение средств'[Направление транзакции] = "Доход"
)
- Итог по расходам подсчитывается следующим образом:
Показатель Расходы =
CALCULATE (
SUM ( 'Распределение средств'[Сумма] ),
'Распределение средств'[Направление транзакции] = "Расход"
)
- Маржа, представляющая собой разницу между доходами и расходами, рассчитывается следующим образом:
Показатель Маржа =
[Показатель Доходы] - [Показатель Расходы]
Рвав-рвав, заметьте, что простое включение промежуточных итогов в визуальном элементе тут не поможет, поскольку по умолчанию визуальный элемент их суммирует. И нужно либо добавлять "минус" искусственно, либо выкручиваться как-то иначе. При этом добавление "минуса" в цифры может понадобиться не везде, а, например, точечно, смотря какой вид имеют данные в источнике, поэтому собака Смайл советует просто сделать необходимый расчет, не надеясь на встроенную логику конкретного визуального элемента.
- Маржинальность есть отношение маржи к общей сумме дохода, ее расчет можно реализовать следующим способом:
Показатель Маржинальность =
DIVIDE ( [Показатель Маржа], [Показатель Доходы], 0 ) * 100
Рвав-рвав, вы не думайте, собака Смайл в курсе волшебной кнопки "Percentage format" ("Процентный формат"), однако "100" написано специально, а вот причина этого будет пояснена далее.
- После завершение необходимых расчетов наконец можно перейти к наиболее интересной части, а именно, получить ответ на вопрос: "Как все это расположить определенным образом?". Для начала нам понадобится специальная мера, получающая единственное значение (ключ), каждой используемой строки. Для этого можно, например, можно использовать функции MAX или MIN, мера будет следующего вида:
Порядок показателя =
MIN ( 'Показатели'[Порядок] )
- Завершающим этапом необходимо создать меру, которая будет использоваться в качестве значения при построении визуальных элементов:
Расчет показателя =
SWITCH (
[Порядок показателя],
1, [Показатель Доходы],
2, [Показатель Расходы],
3, [Показатель Маржа],
4, [Показатель Маржинальность]
)
- Если все сделано правильно, то при построении итоговой таблицы все будет выглядеть наилучшим образом:
Таблица слева – это, собственно, то, что нам требовалось получить.
Таблица справа – это демонстрация того, что все преимущества стандартного элемента (а именно, уровни вложенности, разбиение по столбцам в случае их добавления в визуальный элемент) сохраняются.
Рвав-рвав, теперь пришла пора поговорить о дополнительных хитростях, в студии очередной лайфхак от собаки Смайла:
Как вы уже, наверно, поняли по уровням вложенности, на рисунке использован элемент "Matrix" ("Матрица"), то есть это не таблица, хотя так и выглядит.
Промежуточные итоги отключены в 1-м варианте (таблица слева), поскольку они не нужны, и также надо избавиться от общего итога.
Во 2-м варианте промежуточные итоги нам необходимы, поскольку уровней у нас два, но общий итог нельзя, к сожалению, отключить отдельно, поэтому собаке Смайлу пришлось проявить некоторую изворотливость, по "замазыванию" строки общего итога, его значений, и пр., но, как видите, все получилось.
Причина, по которой при расчете показателя "Маржинальность" в формуле было использовано "*100" вместо процентного формата в том, что, поскольку столбец у нас один, и в нем содержатся показатели разного рода, форматирование меры "Расчет показателя" будет влиять на все строки.
Вот и все, задача реализована.
Рвав-рвав, всем успехов и терпения!
Ваш Смайл