Как добавить процент столбца общего / промежуточного итога в сводную таблицу Excel?

Таблицы и матрицы в Power BI. Расчет процентов и показ пустых значений.

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

Ниже приводятся ответы на следующие вопросы:

1. Как отобразить % на строку в таблице Power BI?
2. Как отобразить в таблице продукты, для которых нет продаж?
3. Почему продукты без продаж все равно не видны в моей таблице?
4. Есть ли аналог таблицы Excel в Power BI?

В Power BI существуют два визуальных элемента «из коробки», которые позволяют отобразить данные в виде таблицы — это таблица (1 на рисунке ниже) и матрица (2 на рисунке ниже). Если проводить аналогию с Microsoft Excel, то таблица близка к обычному листу Excel, а матрица — к сводной таблице.

Рис 1. Значки таблицы и матрицы в Power BI

Таблица — основные возможности.

Помимо внешнего вида и фильтров, здесь есть несколько основных визуальных элементов:

1. Как и по какому столбцу будут отсортированы данные (если нужно отсортировать по более, чем одному столбцу, то следует зажать клавишу Shift)
2. Что будет показано в числовом столбце — фактическое значение (среднее, сумма и т.д.) или % от общего по столбцу (см. рис. 2)
3. Отображать или не отображать измерения, для которых отсутствуют числовые данные (по умолчанию не отображать) — см. рис. 3. Эта настройка актуальна только в том случае, если нет ни одного столбца, для которого включено отображение % от общего по столбцу (см. рис. 4).

Более подробно про функционал таблиц можно почитать в официальной документации: https://docs.microsoft.com/ru-ru/power-bi/visuals/power-bi-visualization-tables

Матрица — поддержка иерархий и % по строкам

Как уже упоминал выше — матрица в Power BI это в какой-то мере аналог сводных таблиц Excel. Здесь уже значительно больше возможностей, чем в таблице. Кратко рассмотрим основные из них:

1. Поддержка иерархий — достаточно добавить несколько измерений в секцию «Строки» или «Столбцы:

1. Проценты могут быть рассчитаны не только для столбца, но и для строки и для целого значения:

Более подробно про функционал матриц можно и нужно почитать в официальной документации: 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 также добавил в эту библиотеку несколько новых функций:

% от суммы по родительской строке (столбцу) — позволяет посчитать долю относительно промежуточного итога по строке или столбцу:

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