Когда ваши данные отсортированы по дате, бывает нужно посчитать нарастающий итог прямо в PQ.
Это можно сделать в три действия:
1) Сортировка данных по дате (по возрастанию, если итог нарастающий)
2) Добавление столбца индекса
3) Расчёт столбца итогов в зависимости от этого индекса.
Подсчёт через списки (столбцы)
Для подсчёта суммы по столбцу мы используем функцию List.Sum. Применять её будем к столбцу "Расход" для всей таблицы из предыдущего шага. Для каждой строки нам нужно взять свою часть таблицы предыдущего шага с помощью функции List.FirstN. Количество взятых строк будет зависеть от индекса, то есть будет своё для каждой строки:
Подсчёт через таблицы
Рассмотрим более сложный вариант и сделаем расчёт нарастающих итогов через таблицы. Для этого нам нужно научиться получать сами таблицы в виде нарастающего итога.
В ячейке первой строки мы получим таблицу с первой строкой,
в ячейке второй строки - с двумя первыми строками,
в третьей строке - с тремя, и так далее.
Для формирования таких таблиц нам пригодится функция, которая будет формировать их в новом столбце:
(table1) => Table.SelectRows(ПредыдущийШаг,
(table2) => table2[Индекс] <= table1[Индекс]).
Фильтруя таблицу по условию:
"Индекс таблицы 2 меньше или равен Индексу таблицы 1",
мы получаем для каждой строки свою вложенную таблицу.
Номер строки будет равен количеству строк вложенной таблицы.
Далее не составит труда получить из вложенной таблицы нужный столбец и суммировать его, что даст нам значение нарастающего итога.
Подсчёт по различным условиям
Вся прелесть подсчёта через таблицы состоит в том, что к условию
"table2[Индекс] <= table1[Индекс]" можно добавлять любые другие условия.
В том числе можно добавить такое условие:
"table2[Кампания] = table1[Кампания]". Это позволит подсчитать нарастающий итог в рамках каждой категории.
Или например такое: "table2[Расход]>4000". Это позволит считать нарастающий итог только по строчкам, где расходы больше 5000 рублей.
Null логично заменить на 0. Он образовался так как первые 5 строчек не подходят по условию.
Вариант с использованием буфера
Чтобы добиться быстродействия на больших объемах данных, воспользуемся функцией Table.Buffer.
Перед добавлением столбца итогов добавляем новый шаг, который буферизует в памяти полученную таблицу с индексом:
Буфер = Table.Buffer(Индекс)
Теперь при добавлении столбца итогов мы будем использовать буферизованную таблицу из шага "Буфер":
На таблице более чем 300 тысяч строк этот способ у меня отработал за 3 минуты. Также добавление столбца можно делать прямо к буферизованной таблице - такой способ отрабатывает еще быстрее.
Если вообще не использовать буфер - операция не выполняется даже в течение 10 минут.
Источник для вдохновения на английском: тут
Канал на YouTube о Power BI: PRO Power BI
Ответы на любые вопросы по Power BI: t.me/PBI_Rus
Свежие новости и статьи в телеграме: t.me/pro_powerbi
Свежие новости и статьи в Facebook: facebook.com/propowerbi
