Хотите, научим вас плохому – как «поломать» итоги в сводной таблице? Судя по вопросам, которые задают нам подписчики – да. Про итоговую строку бывают разные вопросы: как в ней вместо суммы показать среднее, как показать два итога, как показать итог в самом начале. И все это про сводные таблицы Excel или матрицы Power BI.
Это хорошие вопросы. Ответим на них по порядку:
1) как управлять цифрами в итоговой строке;
2) как разместить итоги или промежуточные итоги в начале таблицы или раздела.
И еще разберём, какие вычисления больше подойдут для Power BI, а какие – для Excel.
Ломаем итоговую строку
Итак, нестандартная ситуация: в строке итогов нужно показать какое-то необычное число, отличающееся от основных данных в таблице. Например, в таблице – просто значения, а в итогах или промежуточных итогах должно быть среднее или нарастающая сумма. Или вообще какие-то коэффициенты. В общем, разбираем, как «обмануть» итоговую строку таблицы и показать там что-то неожиданное.
Для управления итогами нам потребуются DAX-формулы, применение которых немного отличается в Power BI и Excel. Примеры таких формул – ISINSCOPE, ISFILTERED и SELECTEDVALUE. И реже HASONEVALUE, ISCROSSFILTERED, HASONEFILTER, FILTERS.
В Power BI оптимальная формула для управления итогами для разных уровней иерархии – ISINSCOPE. С помощью этой формулы можно определить, сгруппированы ли уровни иерархии.
Разберем, как работает эта формула на примере справочника товаров. В справочнике на рисунке слева есть товары, подгруппы и группы.
С помощью ISINSCOPE и конструкции SWITCH + TRUE добавим в таблицу «уровни»: для товаров – символ с кружком, а для групп и подгрупп – текст.
уровень = SWITCH ( TRUE (), ISINSCOPE ( ' спТовары' [товар] ), UNICHAR ( 68178 ),ISINSCOPE ( ' спТовары' [подгруппа] ), " подгруппа" ,ISINSCOPE ( ' спТовары' [группа] ), " группа" ," итог" ) |
По-научному: функция ISINSCOPE возвращает значение ИСТИНА, если столбец включен в контекст фильтра и является столбцом группировки для текущей строки.
Формула для значений в итоговой строке почти такая же – NOT + ISINSCOPE. В логическом отрицании NOT потребуется перечислить все уровни строк в таблице.
уровень итогов = IF ( NOT ( ISINSCOPE ( ' спТовары' [товар] ) ||ISINSCOPE ( ' спТовары' [подгруппа] ) ||ISINSCOPE ( ' спТовары' [группа] ) ),UNICHAR ( 128522 ) ) |
Так, с помощью ISINSCOPE в таблице настраиваются показатели для разных сценариев работы dashboard-а. В примерах выше – смайлы и текст, а в рабочих отчетах – меры.
Формула ISINSCOPE оптимизирована для работы в Power BI, а вот в Power Pivot для Excel такой формулы нет. Вместо нее обычно применяется ISFILTERED.
Важное ограничение для ISFILTERED: чтобы полностью управлять итогами, фильтруемые данные не должны добавляться в срезы или фильтроваться другими элементами отчета. На рисунке ниже видно, что в формуле используется ISFILTERED и на срезе выбраны значения.
Из-за фильтрации срезом в итоговой строке, созданной с помощью ISFILTERED, вместо ожидаемой надписи «итог» стоит слово «группа». Чтобы в строке итогов показывать нужное значение (слово «итог»), в справочники добавляются дубликаты столбцов, которые затем потребуется скрыть. Поэтому ISINSCOPE – более удобный вариант для управления итогами, чем ISFILTERED.
Управление размещением строки итогов
Итак, еще одна нестандартная ситуация: строку итогов нужно показать в начале таблицы. Или добавить промежуточные итоги в начале каждого раздела. Кстати, несколько наших читателей говорили, что на поиск таких решений тратили много времени – от нескольких дней. Разбираемся, как добавить итоговую строку там, где это не предусмотрено стандартными настройками, а вам очень нужно.
На самом деле тут тоже все просто – потребуется дополнительный столбик в справочнике. Здесь лучше не создавать «просто» столбик, который больше нигде не пригодится, а добавить что-то полезное. Например, добавить в таблицу дат (Календарь) столбец, который будет делить периоды на прошедшие и будущие с помощью формулы TODAY().
Добавляем в таблицу дат (Календарь) вычисляемый столбец с прошедшими и будущими датами:
период = IF ( [Date] <= TODAY(), " Actual" , " Future" ) |
или так:
период = IF ( [Date] <= MAX ( ' данные' [дата] ) , " Actual" , " Future" ) |
Теперь этот столбец подойдет для группировки и создания дополнительных итогов. Просто добавьте эту группу в таблицу и отключите вывод общих итогов.
С помощью ISINSCOPE обычные показатели в строке итогов можно заменить на что-то другое.
цена like = IF ( NOT ( ISINSCOPE ( ' Календарь' [YYYY-MM] ) ),IF ( [цена] >= 15000, UNICHAR ( 128515 ), UNICHAR ( 128528 ) ), [цена] ) |
Размещение показателей в таблицах также может быть связано с настройкой разделов. Например, стандартные настройки таблиц позволяют выводить значения в строках с помощью галочки «Переключить значения на строки».
Иногда требуется вывод показателей таблицы в разделах. Такое представление тоже настраивается с помощью DAX-формул и конструкции SWITCH + TRUE.