Как добавить процент столбца общего / промежуточного итога в сводную таблицу Excel?
Таблицы и матрицы в Power BI. Расчет процентов и показ пустых значений.
Я всегда думал, что никому не нужны сообщения о базовой функциональности, но вопросы, которые постоянно появляются в социальных сетях, заставляют меня думать, что я был неправ.
Ниже приводятся ответы на следующие вопросы:
- Как отобразить % на строку в таблице Power BI?
- Как отобразить в таблице продукты, для которых нет продаж?
- Почему продукты без продаж все равно не видны в моей таблице?
- Есть ли аналог таблицы Excel в Power BI?
В Power BI существуют два визуальных элемента «из коробки», которые позволяют отобразить данные в виде таблицы — это таблица (1 на рисунке ниже) и матрица (2 на рисунке ниже). Если проводить аналогию с Microsoft Excel, то таблица близка к обычному листу Excel, а матрица — к сводной таблице.
Рис 1. Значки таблицы и матрицы в Power BI
Таблица — основные возможности.
Помимо внешнего вида и фильтров, здесь есть несколько основных визуальных элементов:
- Как и по какому столбцу будут отсортированы данные (если нужно отсортировать по более, чем одному столбцу, то следует зажать клавишу Shift)
- Что будет показано в числовом столбце — фактическое значение (среднее, сумма и т.д.) или % от общего по столбцу (см. рис. 2)
- Отображать или не отображать измерения, для которых отсутствуют числовые данные (по умолчанию не отображать) — см. рис. 3. Эта настройка актуальна только в том случае, если нет ни одного столбца, для которого включено отображение % от общего по столбцу (см. рис. 4).
Более подробно про функционал таблиц можно почитать в официальной документации: https://docs.microsoft.com/ru-ru/power-bi/visuals/power-bi-visualization-tables
Матрица — поддержка иерархий и % по строкам
Как уже упоминал выше — матрица в Power BI это в какой-то мере аналог сводных таблиц Excel. Здесь уже значительно больше возможностей, чем в таблице. Кратко рассмотрим основные из них:
- Поддержка иерархий — достаточно добавить несколько измерений в секцию «Строки» или «Столбцы:
- Проценты могут быть рассчитаны не только для столбца, но и для строки и для целого значения:
Более подробно про функционал матриц можно и нужно почитать в официальной документации: https://docs.microsoft.com/ru-ru/power-bi/visuals/desktop-matrix-visual
Добавить комментарий Отменить ответ
Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются данные ваших комментариев.
Настройка вычислений в сводных таблицах
Допустим, у нас есть сводная таблица, построенная с результатами анализа продаж по месяцам для разных городов (если нужно, прочитайте эту статью, чтобы понять, как их создавать в целом или освежить в памяти):
Нам хочется слегка изменить ее внешний вид, чтобы она отображала нужные вам данные более наглядно, а не просто вываливала кучу чисел на экран. Что для этого можно сделать?
Другие функции расчета вместо банальной суммы
Если щелкнуть правой кнопкой мыши по расчетному полю в области данных и выбрать из контекстного меню команду Параметры поля(Field Settings) или в версии Excel 2007 – Параметры полей значений(Value Field Settings) , то откроется очень полезное окно, используя которое можно задать кучу интересных настроек:
В частности, вы можете легко изменить функцию расчета полей на среднее значение, минимум, максимум и т.д. Например, если мы изменим количество в нашей сводной таблице на количество, то вместо общей выручки мы увидим количество транзакций для каждого товара:
По умолчанию для числовых данных Excel всегда автоматически выбирает суммирование (Sum), а для нечисловых данных (даже если из тысяч ячеек с числами или с текстом или числом в текстовом формате есть хотя бы одна пустая) — функцию подсчета количества значений (Count).
Если вы хотите увидеть среднее, общее и количество, т.е. несколько функций вычисления для одного и того же поля в сводной таблице, смело опускайте нужное поле в область данных несколько раз подряд, чтобы получить что-то подобное:
…а потом задавайте разные функции для каждого из полей, щелкая по очереди по ним мышью и выбирая команду Параметры поля(Field settings) , чтобы в итоге получить желаемое:
Долевые проценты
Если в том же окне настроек поля нажать кнопку Параметры или перейти на вкладку Дополнительные вычисления (в Excel 2007-2010), то появится раскрывающийся список Показать данные как:
Выбрав % от строки, % от столбца или % от общего количества, вы можете автоматически рассчитать процентное соотношение для каждого продукта или города. Вот как выглядит наша сводная таблица с включенным параметром % от столбцов:
Динамика продаж
Если в выпадающем списке Show data as выбрать Difference, а в нижних полях Base field и Base element выбрать Month и Back (в родной английской версии это странное слово было заменено на Previous):
. затем получаем сводную таблицу, которая показывает разницу продаж каждого последующего месяца от предыдущего, то есть — динамику продаж:
А если заменить Difference на % разницы и добавить условное форматирование для выделения красным цветом отрицательных значений, то получится то же самое, но не в рублях, а в процентах:
Щелкнув правой кнопкой мыши на поле в Microsoft Excel 2010 и выбрав в контекстном меню пункт Summarize Values By, можно легко применить все вышеперечисленные настройки:
. y Дополнительные расчеты (показать данные как) :
Excel 2010 также добавил в эту библиотеку несколько новых функций:
- % от суммы по родительской строке (столбцу) — позволяет посчитать долю относительно промежуточного итога по строке или столбцу:
В предыдущих версиях можно было рассчитать только соотношение к общему количеству.