Файлы к уроку:
Ссылки:
Описание
В этом уроке сначала мы познакомимся с функцией EARLIER на примере добавления вычисляемого столбца с рейтингом товарной категории по выручке.
После этого мы добавим в таблицу справочника товаров столбец с ABC категорией в зависимости от суммы продаж.
Решение
Знакомство с EARLIER на примере создания вычисляемого столбца с рангом товарной категории
В модели данных находится только одна таблица subcategory. Добавим в эту таблицу столбец с рангом.
Чтобы вычислить ранг нужно для каждой строки найти сколько значений subcategory_sales большей этого значения и прибавить единицу.
В Power Pivot в вычисляемом столбце вводим формулу:
=
COUNTROWS (
FILTER (
subcategory;
EARLIER ( subcategory[subcategory_sales] ) < subcategory[subcategory_sales]
)
) + 1Функция FILTER фильтрует таблицу по условию из параметра filter. Внутри функции FILTER ссылка subcategory[subcategory_sales] обращается ко всему столбцу таблицы, а EARLIER ( subcategory[subcategory_sales] ) обращается только к выбранной строке. Выходит, что функция EARLIER позволяет нам внутри фильтра сослаться на текущую строку таблицы, а не на весь столбец целиком.
ABC анализ
В модели данных 5 таблиц: 1 таблица факта и 4 справочника. В данном примере нам пригодятся только таблицы fact_sales, dim_products. В таблицу dim_products мы добавим столбец с категорией ABC в зависимости от суммы продаж.
Сначала в таблицу dim_products добавим сумму продаж каждого продукта.
[Sum of Sales]
=CALCULATE(SUM(fact_sales[SalesAmount]))Дальше найдем общую сумма продаж всех продуктов.
[Total Sum of Sales]
=SUM(dim_products[Sum of Sales])Дальше создадим столбец с кумулятивной суммой. Для каждого продукта мы вычислим сколько продуктов имеют такую же или большую сумму продаж.
[Accumulated Sum of Sales]
=CALCULATE (
SUM ( dim_products[Sum of Sales] );
ALL ( dim_products );
dim_products[Sum of Sales] >= EARLIER ( dim_products[Sum of Sales] ))
Вычислим кумулятивный процент. Просто разделим кумулятивную сумму на общую сумму продаж.
[Accumulated Sum of Sales %]
=dim_products[Accumulated Sum of Sales]/dim_products[Total Sum of Sales]Осталось только присвоить категорию A, B или C.
[ABC]
=SWITCH (
TRUE ();
dim_products[Accumulated Sum of Sales %] <= 0,7; "A";
dim_products[Accumulated Sum of Sales %] <= 0,9; "B";
"C"
)
Примененные функции
- ALL
- CALCULATE
- COUNTROWS
- EARLIER
- FILTER
- SUM
- SWITCH
- TRUE
Практический курс Power Pivot
| Номер урока | Урок | Описание |
|---|---|---|
| 1 | Power Pivot Практический №1. Значение показателя на конец месяца (ENDOFMONTH, CALCULATE) | В этом уроке вы узнаете как находить последнее значение показателя на конец месяца. С подобным приходится сталкивать часто, особенно когда речь о финансовых показателях, например, состояние кредитного портфеля. |
| 2 | Power Pivot Практический №2. Нарастающий итог, Анализ клиентской базы (CALCULATE, ALLEXCEPT, ALL, FILTER) | В этом уроке мы научимся считать нарастающий итог на примере анализа роста клиентской базы. Задача прикладная и интересная. |
| 3 | Power Pivot Практический №3. Анализ лояльности клиентов | В этом уроке мы проанализируем нашу клиентскую базу. |
| 4 | Power Pivot Практический №4. Анализ лояльности клиентов 2 | Проанализируем структуру продаж. Разобьем клиентов на группы в зависимости от года первой сделки. |
| 5 | Power Pivot Практический №5. Анализ лояльности клиентов 3 — сколько прошло до второго заказа | Посчитаем количество клиентов, которые сделали второй заказ через 0, 1, 2, 3 и т. д. квартала. |
| 6 | Power Pivot Практический №6. Сравнение всех категорий с выбранной | Научимся сравнивать продажи выбранной категории с остальными. |
| 7 | Power Pivot Практический №7. Динамический фильтр Топ N (HASONEVALUE, RANKX, ALL, IF) | В этом уроке вы узнаете как создать динамический фильтр Топ N, чтобы отображать в сводной таблице только несколько лучших значений. |
| 8 | Power Pivot Практический №8. Функция EARLIER, ABC анализ | Выполним ABC категоризацию в Power Pivot. |
