Post-Image

Построчное построение отчета

Рвав-рвав, собака Смайл снова взялся за перо!

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

Способ есть, он довольно муторный, но рабочий)

Не секрет, что таблица (или матрица), используется практически повсеместно. Но часто построение таблиц “как есть” не устраивает конечных пользователей, и обычно требуется осуществление дополнительных расчетов. Power BI, в отличие от Excel, не может напрямую работать с ячейкой, он работает только со столбцом, и поэтому требование конечного пользователя “собрать кучу различных показателей в одной форме” выполнить весьма затруднительно. То есть замечательная последовательность: “Берем это и это, складываем, минусуем вон то, а полученный результат умножаем на такой-то коэффициет” вполне может вам встретиться в реальной жизни :-) А в качестве “добивания” будет: “Хочу таблицу именно в таком виде, поскольку формы нашей отчетности (та-дам!) утверждены…”.

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

Итак, как описанное выше выглядит на практике?

Пример:

Имеется таблица операций, содержащая набор реквизитов следующего вида:

– дата операции;

– описание операции (в нашем примере это какая-то строка отчета, то есть одна из его статей или показателей);

– аналитика операции (в нашем примере это направление статьи);

– сумма операции.

art_007_screen_1

  • Календарь выделен в качестве отдельной таблицы.

  • Также для удобства создана отдельная таблица для хранения мер, хоть теперь и можно делать группировки сущностей по папочкам внутри таблицы.

    Задача:

    На основе представленных данных построить отчет табличного вида, строки которого сгруппированы в следующем порядке:

    – На 1-м месте расположены статьи доходов;

    – На 2-м месте расположены статьи расходов;

    – На 3-м месте выведен рассчетный показатель “Маржа”;

    – На 4-м месте выведен рассчетный показатель “Маржинальность”;

    Как вы понимаете, при разработке какого-либо отчета есть 2 пути:

    – Строить расчеты на основании расчетных столбцов;

    – Воспользоваться мерами.

    Каждый вариант имеет свои плюсы и минусы. Например, при использовании мер, в отличии от расчетного столбца, возможность “drill down” до исходной операции в строке отчета пропадает. Увы, но это так работает. Зато часть рассчетов, которые возможно сделать при помощи мер, реализовать с использованием рассчетных столбцов или тяжело, или невозможно. Например, расчеты накопительных итогов в течении длительного периода времени.

    Рвав-рвав, применительно к обозначенной задаче, можно, конечно, иди и 1-м путем, однака, основываясь на опыте хозяина, как-то построевшего подобный отчет при помощи расчетных столбцов, собака Смайл предлагает использовать меры, и необходимо заранее продумать требования, поступившие от клиента, чтобы выстроить в голове правильную структуру отчета.

    Решение:

  • Создана таблица “Indicators”, содержащая список строк будущего отчета, и их порядок.

    Рвав-рвав, предупреждаю сразу:

    Поскольку структура жесткая, номера, в реальности, можно присваивать не по порядку, а с запасом, то есть “10”, “20”, и так далее, дабы потом, когда вас попросят что-то добавить, не было мучительно больно, и не пришлось переделывать все. Это я вам как опытный собакен говорю, хоть собаку (хе-хе), на этом и не ел!

    art_007_screen_2

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

  • В нашем случае у нас будет две составные статьи, которые одновременно, будут являться и конечным итогом. Для определения итога по доходам можно написать меру следующего вида:

    Показатель Доходы = 
    CALCULATE ( SUM ( 'Dataset'[Amount] ); 'Dataset'[Transaction] = "Доход" )

    Итог по расходам подсчитывается следующим образом:

    Показатель Расходы = 
    CALCULATE ( SUM ( 'Dataset'[Amount] ); 'Dataset'[Transaction] = "Расход" )

  • Маржа, представляющая собой разницу между доходами и расходами, рассчитывается как:

    Показатель Маржа =
    [Показатель Доходы] - [Показатель Расходы]

    Заметьте, что простое включение промежуточных итогов в визуальном элементе тут не поможет, поскольку по умолчанию визуальный элемент их суммирует. И нужно либо добавлять “минус” искуственно, либо выкручиваться как-то иначе. При этом добавление “минуса” в цифры может понадобиться не везде, а, например, точечно, смотря какой вид имеют данные в источнике, поэтому собака Смайл советует просто сделать необходимый расчет, не надеясь на встроенную логику конкретного визуального элемента.

  • Маржинальность есть отношение маржи к общей сумме дохода, ее рассчет можно реализовать следующим способом:

    Показатель Маржинальность =
    DIVIDE ( [Показатель Маржа]; [Показатель Доходы]; 0 ) * 100

    Рвав-рвав, вы не думайте, собака Смайл в курсе волшебной кнопки “Percentage format” (“Процентный формат”), однако “х100” написано специально, а вот причина этого будет пояснена далее.

  • После завершение необходимых расчетов наконец можно перейти к наиболее интересной части, а именно, получить ответ на вопрос: “Как все это расположить определенным образом?”. Для начала нам понадобится специальная мера, получающая единственное значени (ключ), каждой используемой строки. Для этого можно, например, можно использовать фунции MAX или MIN, мера будет следующего вида:

    Порядок показателя =
    MIN ( Indicators[Порядок] )

  • Завершающим этапом необходимо создать меру, которая будет использоваться в качестве значения при построении визуальных элементов.

    Расчет показателя =
    SWITCH (
        [Порядок показателя];
        1; [Показатель Доходы];
        2; [Показатель Расходы];
        3; [Показатель Маржа];
        4; [Показатель Маржинальность]
    )

  • Если все сделано правильно, то при построении итоговой таблицы все будет выглядеть наилучшим образом:

    – таблица слева – это, собственно, то, что нам требовалось получить;

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

    art_007_screen_3

    Рвав-рвав, теперь пришла пора поговорить о дополнительных хитростях, в студии очередной лайфхак от собаки Смайла:

    – как вы уже, наверно, поняли по уровням вложенности, на рисунке использован элемент “Matrix” (“Матрица”), то есть это не таблица, хотя так и выглядит;

    – промежуточные итоги отключены в 1-м варианте (таблица слева), поскольку они не нужны, и также надо избавиться от общего итога;

    – во 2-м варианте промежуточные итоги нам необходимы, поскольку уровней у нас два, но общий итог нельзя, к сожалению, отключить отдельно, поэтому собаке Смайлу пришлось проявить некоторую изворотливость, по “замазыванию” строки общего итога, его значений, и пр., но, как видите, все получилось;

    – причина, по которой при расчете показателя Маржинальность было использовано “x100” вместо процентного формата в том, что, поскольку столбец у нас один, и в нем содержатся показатели разного рода, форматирование меры “Расчет показателя” будет влиять на все строки.

    Рвав-рвав, всем успехов и терпения!

    Ваш Смайл