Статья подготовлена в рамках сериала «Залетай в BI»

На прошлой недели Руслан делал задачки на табличные функции и сделал ошибку при работе с функцией TOTAL(). Объясняю в чем она заключалась и как это работает.

Функций TOTAL() — уникальная функция в Табло, которая работает по своим правилам. Если их не знать, то легко сделать ошибку. Рассмотрим на примерах.

TOTAL() игнорирует дименшены в виде

Предположим, что у нас есть средние чеки по разным категориям товаров и мы хотим посчитать отклонение это среднего чека от среднего между этими категориями.

Сначала посчитаем средний чек между категориями. Для этого можем кинуть референс лайн со средним на вид:

Получили 445$ — это значение получается как (504+532+96+649)/4=445. А каждое слагаемое — это AVG(Sales) по всем строчкам данных внутри этой категории. То есть мы посчитали среднее от средних.

Теперь попробуем воспроизвести среднее от средних с помощью функции TOTAL():

Получим то, что значение считается по-другому, вместо 445, мы видим 350. Почему же так происходит? Функция TOTAL игнорирует все димешены в виде при расчёте и считает среднее на уровне строк базы данных. То есть мы по сути получили такой расчёт, как если бы в виде не было ни одного дименшена:

Игнорирование дименшенов — самое главное отличие функции TOTAL(), в отличии от других табличных вычислений она не зависит от разбивки на листе.

Если бы мы хотели посчитать средне от среднего нам бы было необходимо использовать функцию WINDOW_AVG(). Эта функция уже не игнорирует дименшены, а делает расчеты между агрегированными значениями, которые получились по каждому из дименшенов.

Поэтому если бы мы хотели построить график отклонения от среднего между категориями, а не от среднего в целом, то нам бы нужно было использовать именно эту функцию. Могли бы получить какую-то такую табличку:

Какой использовать из расчетов зависит от вашей задачи и, возможно, TOTAL() был бы даже логичнее использовать в данном кейсе. Но очень важно понимать как работает эта функция в данном случае.

Grand Total использует TOTAL()

Предположим, что мы хотим вывести таблицу в которой будут посчитаны количество уникальные товаров по городам (считаем, что название товара == его ID):

Теперь добавим итог для этой таблицы. Получим 110 товаров.

Пользователи, привыкшие к Экселю, подумают, что в итоге мы увидим сумму значений по строкам, но на самом деле это не так. Все итоги в Табло тоже используют функцию TOTAL() и так как мы считаем неаддитивную метрику, то он посчитает количество уникальных товаров среди всех городов. Чтобы переключить способ расчета на сумму, необходимо выбрать опцию Total using (таким образом на самом деле меняем расчет на WINDOW_SUM()). При это получим другой результат (114, а не 110)

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

Итого

Оба примера которые я привел в заметке очень часто встречаются в реальной жизни при расчетах DAU, LTV, Retention и других неаддитивных метрик. Будьте аккуратны и случайно не посчитайте что-то не то, что ожидает ваш пользователь.