Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):

• DAX функция DIVIDE
• DAX функция QUOTIENT
• DAX функция MOD

---

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы поговорим о том, как в Power BI (Power Pivot) защититься от ошибки деления на 0. А также, о том, как из общей суммы секунд вычислить соответствующее число часов, минут и секунд? А если говорить конкретнее, то разберем функции деления в DAX: DIVIDE (деление на ноль), QUOTIENT (целочисленное деление) и MOD (остаток от целочисленного деления).

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

Да, и еще один момент, в рамках распродажи до 30 апреля 2024 г. у Вас имеется возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 50% (вместо 10000, всего за 5000 руб.)

В этом видеокурсе язык DAX преподнесен как простой конструктор, состоящий из нескольких блоков, которые имеют свое определенное, конкретное предназначение. Сочетая различными способами эти блоки, Вы, при помощи конструктора формул DAX, с легкостью сможете решать любые (простые или сложные) аналитические задачи.

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 50% (до 30 апреля 2024 г.): узнать подробнее

до конца распродажи осталось:

 

DAX функция DIVIDE в Power BI и Power Pivot

DIVIDE () — производит деление с обработкой ошибки «деление на 0». Обработка ошибки заключается в выводе альтернативного результата в случае возникновения ситуации деления на ноль.

Синтаксис:

DIVIDE (Делимое Число; Делитель; Альтернатива)

Где, альтернатива — (необязательный параметр) значение, которое нужно вывести в случае ошибки деления на ноль (0). По умолчанию выводится пустое значение BLANK ().

Пример формулы на основе DAX функции DIVIDE.

В Power BI Desktop имеется исходная таблица «Реклама», содержащая по каждой дате затраты на рекламу и прибыль, полученную от продаж с этой рекламы:

 

Задача — создать в Power BI меру расчета коэффициента ROI (окупаемости затрат на рекламу). Данный коэффициент рассчитывается как сумма всей прибыли деленная на сумму всех затрат.

Сумму значений мы можем рассчитать при помощи DAX функции SUM.

В итоге, формула расчета ROI будет такой:

ROI = SUM ('Реклама'[Прибыль]) / SUM ('Реклама'[Затраты])

То есть, мы сложили всю прибыль, сложили все затраты и затем разделили сумму прибыли на сумму затрат.

Вроде бы, все хорошо, но, если мы вынесем эту меру в отчеты Power BI и посмотрим ROI по дням, то в визуализации в одной из строк будет отображаться непонятное слово «Бесконечность»:

 

А все дело в том, что у нас произошла ошибка деления на ноль — прибыль 18000 была разделена на затраты, равные 0. И Power BI вместо этой ошибки вывела значение «Бесконечность».

Для того, чтобы исправить эту ситуацию, в формуле расчета ROI вместо обычного деления нужно использовать рассматриваемую DAX функцию DIVIDE. Она позволит нам произвести деление и обработать все ошибки, возникающие при делении на о. И вместо значения «Бесконечность» вывести то значение, которое нам нужно, например, пустое значение BLANK ().

В итоге, формула расчета ROI на основе функции DIVIDE будет такая:

ROI = 
DIVIDE (
    SUM ('Реклама'[Прибыль]);
    SUM ('Реклама'[Затраты])
)

Где, в первом параметре функции DIVIDE мы указали сумму прибыли, которую нужно разделить, во втором параметре — сумму затрат, на которую делится сумма прибыли. Третий параметр указывать не стали, так как по умолчанию он равен функции BLANK (), что нам и нужно.

В результате, визуализация в Power BI теперь работает правильно:

 

Используйте функцию деления DIVIDE всегда, когда имеется хоть малейший потенциал значения нуля в делителе Вашей формулы.

 

DAX функция QUOTIENT в Power BI и Power Pivot

QUOTIENT () — выполняет деление чисел, входящих в параметры функции и возвращает целочисленную часть от деления.

Синтаксис:

QUOTIENT (Делимое Число; Делитель)

Пример формулы на основе DAX функции QUOTIENT.

В Power BI имеется исходная таблица «Общая Длительность Звонков», содержащая в себе информацию по общей сумме секунд всех разговоров менеджеров:

 

Требуется рассчитать это количество в целых часах.

Для этого, общее количество секунд нужно разделить на количество секунд в часе (3600). Но, в результате этого деления получится дробное число, а нам нужна только целая часть результата деления. В этой ситуации нам поможет функция QUOTIENT.

В итоге, формула расчета общей длительности звонков в целых часах будет такой:

Длительность В Часах = 
QUOTIENT (
    SUM ('ОбщаяДлительностьЗвонков'[КоличествоСекунд]);
    3600
)

И в отчете Power BI по каждому менеджеру эта мера выведет количество целых часов, которые затратили менеджеры на звонки:

 

DAX функция MOD в Power BI и Power Pivot

MOD () — возвращает остаток от деления со знаком делителя.

Синтаксис:

MOD (Делимое Число; Делитель)

В качестве разбора формулы на основе DAX функции MOD продолжим рассматривать прошлый пример с расчетом длительности разговора менеджеров по телефону в целых часах на основе общей суммы в секундах.

Разделив общее количество секунд на 3600 при помощи QUOTIENT, мы получили целую часть от деления. Но, также, при этом делении мы можем получить и остаток от этого целочисленного деления (в нашем случае, это оставшееся количество секунд за вычетом целых часов из общего количества секунд).

И сделать это можно функцией MOD:

Остаток Секунд = 
MOD (
    SUM ('ОбщаяДлительностьЗвонков'[КоличествоСекунд]);
    3600
)

Давайте проверим эту формулу в Power BI:

 

Действительно, если мы рассмотрим менеджера Петров из визуализации выше, то 16784 (общее количество секунд) — 14400 секунд (4 часа * 3600) = 2384 (остаток секунд). И функция MOD нам также вывела данное значение 2384.

Теперь, это получившееся значение (остаток секунд) можно еще раз разделить функцией QUOTIENT на 60 и мы получим целое количество минут из этого остатка секунд:

Остаток В Минутах = 
QUOTIENT (
    MOD (
        SUM ('ОбщаяДлительностьЗвонков'[КоличествоСекунд]);
        3600
    );
    60
)

В Power BI вычисление этой формулы будет таким:

 

Давайте проверим все вычисления на примере менеджера Петрова: общее количество секунд у него 16784, а 4 часа и 39 минут, это 16740 секунд (60*(4*60+39). Все правильно 16740 входит в общее количество секунд 16784.

Теперь, осталось вычислить окончательный остаток в секундах:

ОстатокВСекундах = 
SUM ('ОбщаяДлительностьЗвонков'[КоличествоСекунд])
- [ДлительностьВЧасах] * 3600
- [ОстатокВМинутах] * 60

И в Power BI все это будет выглядеть так:

 

Напоследок, осталось навести некий «косметический дизайн» — совместим часы, минуты и секунды в единое значение «часы : минуты : секунды», для этого, воспользуемся оператором объединения в языке DAX — & и текстом с двоеточием («:»):

ДлительностьЗвонков = 
[ДлительностьВЧасах] & ":" & [ОстатокВМинутах] & ":" & [ОстатокВСекундах]

Итоговая визуализация, демонстрирующая совместную работу DAX функций QUOTIENT и MOD в Power BI будет такая:

 

Таким образом, общее количество секунд, затраченное менеджером на звонки мы превратили в соответствующее отображение в часах, минутах и секундах.

Давайте проверим все вычисления, так сказать, на калькуляторе, на примере менеджера Сидоров:

Длительность звонков = 5:9:29, то есть — 5 часов, 9 минут и 29 секунд, что равно 18569 секунд (5*3600+9*60+29). А это, в свою очередь, равно исходной сумме секунд по менеджеру Сидоров (18569 секунд).

На этом, с разбором DAX функций DIVIDE (деление на ноль «0»), QUOTIENT (целочисленное деление) и MOD (остаток от целочисленного деления) в Power BI и Power Pivot, все.

Также, напоминаю Вам, в рамках распродажи до 30 апреля 2024 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 50% (вместо 10000, всего за 5000 руб.)

В этом видеокурсе язык DAX преподнесен как простой конструктор, состоящий из нескольких блоков, которые имеют свое определенное, конкретное предназначение. Сочетая различными способами эти блоки, Вы, при помощи конструктора формул DAX, с легкостью сможете решать любые (простые или сложные) аналитические задачи.

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 50% (до 30 апреля 2024 г.): узнать подробнее

До конца распродажи осталось:


 
 
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
 
 
 
 

Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.
 
Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.

 
Понравился материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

до конца распродажи осталось: