Power Query Power Query. Практический курс

Power Query Практический №7. Столбец общей суммы в PQ и PP

Файлы к уроку:

Ссылки:

Описание

В этом уроке мы создадим столбец с общей суммой в Power Query и в Power Pivot, а потом найдем процент продаж от общей суммы по категории.

Решение

Выполнить группировку по категории товаров и суммировать валовую прибыль
Создать столбец общей суммы при помощи ссылки на таблицу из предыдущего шага и функции List.Sum(). В нашем случае формула в столбце будет такая List.Sum(Сортировка[Валовая прибыль]).
Добавить столбец с долей категории в общей сумме.

Теперь решим эту же задачу в Power Pivot. Для начала загрузим исходную таблицу в модель данных.

Создать сводную таблицу и добавить в строки категории товаров
Создать меру, которая будет суммировать валовую прибыль. В этой мере будет просто использоваться функция SUM
Создать меру при помощи CALCULATE, которая в первом аргументы будет использовать меру из предыдущего шага, а во втором аргументы при помощи ALL отключит все фильтры
Теперь осталось лишь добавить столбец, который разделит первую меру на вторую

Вот мы и решили задачу по созданию столбцу общей суммы и нахождению процента от суммы по категории в Power Query и в Power Pivot.

Вы можете заметить, что в Power Pivot эта задача выполняется в разы быстрее. Все агрегирования лучше делать в Power Pivot, если есть такая возможность.

Примененные функции

Table.TransformColumnTypes
Int64.Type
Table.Group
List.Sum
Table.Sort
Order.Descending
Table.AddColumn
Number.Type
Percentage.Type

Код

Получаем данные:

let
   source = Excel.CurrentWorkbook(){[Name = "ККМтовар"]}[Content],
   types = Table.TransformColumnTypes(
      source,
      {
         {"Дата", type date},
         {"Количество", Int64.Type},
         {"Себестоимость", type number},
         {"Цена", type number},
         {"Скидка", type number},
         {"Сумма продаж", type number},
         {"Валовая прибыль, руб.", type number}
      }
   )
in
   types

Находим процент каждой категории от общей суммы:

let
   source = data,
   table_group = Table.Group(
      source,
      {"Группа товара"},
      {
         {
            "Валовая прибыль",
            each List.Sum([#"Валовая прибыль, руб."]),
            type number
         }
      }
   ),
   table_sort = Table.Sort(
      table_group,
      {{"Валовая прибыль", Order.Descending}}
   ),
   table_add_column = Table.AddColumn(
      table_sort,
      "Валовая прибыль Всего",
      each List.Sum(table_sort[Валовая прибыль]),
      Number.Type
   ),
   table_add_column_2 = Table.AddColumn(
      table_add_column,
      "Доля",
      each [Валовая прибыль] / [Валовая прибыль Всего],
      Percentage.Type
   )
in
   table_add_column_2

Этот урок входит в Практический курс Power Query

Номер урока	Урок	Описание
1	Обработка типичной выписки	В этом уроке мы обработаем типичную банковскую выписку.
2	Интересная консолидация	В этом уроке мы будем практиковать объединение таблиц по вертикали.
3	Множественная консолидация при несовпадении заголовков	Объединим таблицы по вертикали, когда заголовки таблиц не совпадают, но порядок всегда одинаков.
4	Таблица дат	Для построения отчетов в модели данных Excel вам нужно создать таблицу дат. Сейчас вы узнаете как это сделать в Power Query.
5	Продажи год назад	В этом уроке мы узнаем как в Power Query получить таблицу с продажами прошлого года напротив текущих.
6	Консолидация книг и листов одновременно	В этом уроке мы научимся объединять все листы всех книг, т. е. выполним двухуровневую консолидацию.
7	Столбец общей суммы в PQ и PP	В этом уроке мы создадим столбец, в котором будет находиться общая сумма всех строк таблицы. Так же разберем эту же операцию в Power Pivot.
8	Скученные данные 3	В этом уроке мы обработаем еще один файл со скученными данными. На этот раз в одном столбце находятся даты и номенклатура. Нужно разбить этот столбец на два.
9	Строки преобразовать в столбцы	У нас есть таблица, в которой один столбец имеет скученные данные. Нам нужно скученный столбец преобразовать в столбцы.
10	Пивот, анпивот, группировка	В этом уроке мы попрактикуем анпивот, условную логику и группировку.
11	Нужные столбцы с нужного листа	В этом уроке мы научимся извлекать нужные столбцы с нужного листа не используя название самого листа.
12	Объединение, группировка, транспонирование, анпивот	В этом уроке на очень интересном примере из реальной жизни попрактикуем несколько техник Power Query: группировка, анпивот, объединение таблиц по горизонтали, транспонирование.
13	Нарастающий итог 4, много группировок	В этом уроке мы изучим еще 1 способ создать столбец нарастающего итога в Power Query. В отличие от других способов здесь мы не будем пользоваться формулами. Все сделаем при помощи пользовательского интерфейса.
14	Количество позиций в строке	В этом уроке мы посчитаем количество наименований, перечисленных в одной ячейке.
15	List Contains, Пользовательская функция	В этом уроке повторим создание пользовательских функций и рассмотрим еще 1 пример использования функции List.Contains.
16	ABC анализ	Научимся выполнять ABC анализ в Power Query.

предыдущая запись

Power Query Ошибки №5. Количество столбцов CSV

следующая запись