Примеры

Пример 1. Расчет выручки в разбивке по магазинам и по продажам со скидками

1. Выручка считается по формуле:
   Количество товара в заказе умножить на цену за единицу товара, минус скидка в заказе. Для построчного вычисления используем SUMX:
   SUMX(factonlinesales, factonlinesales[unitprice]*factonlinesales[salesquantity] - factonlinesales[discountamount])
   

2. Для визуализации используем гистограмму. На нее выведем нашу меру в разбивке по магазинам:
   

   

3. Для того чтобы отобразить данные по продажам, по которым были акции, используем виджет фильтр:
   

   

4. Если есть необходимость рассчитать сразу продажи со скидками, без использования фильтра, будем использовать следующую формулу:
   

   CALCULATE(SUMX(factonlinesales, factonlinesales[unitprice]*factonlinesales[salesquantity] - factonlinesales[discountamount]), FILTER(dimpromotion,dimpromotion[promotioncategory] IN{"Store"})
   )

   
   

   

Пример 2. Расчет доли продаж по продуктам внутри бренда

1. Сначала рассчитаем доли продаж по брендам.
   Removefilters в знаменателе отменяет все фильтры по измерению dimproduct, таким образом, доля считается корректно:
   

   

2. Теперь добавим в разбивку продукт. Мера будет считать долю каждого продукта от общей суммы продаж:
   

   

3. Теперь мы посчитаем долю каждого продукта внутри бренда. Для этого мы сгруппируем продажи по бренду с помощью функции SUMMARIZE:
   

   

Пример 3. Расчет итоговой прибыли с учетом типа клиента

Рассчитывать прибыль будем как для физических лиц, у которых имеется скидка, указанная в столбце discountamount, так и для юридических лиц, у которых также есть скидка, указанная в столбце bonus.

Используемые данные:

Для расчета прибыли для физически лиц, создадим меру Прибыль для физ. лиц и введем следующую формулу:

Прибыль для физ. лиц = CALCULATE (SUMX (fact, fact[salesamount] - fact[discountamount]), FILTER (dimcustomer, dimcustomer[customertype] IN {"Person"} ))

где:

• fact[discountamount] – скидка, которая вычитается из общей суммы продаж fact[salesamount];

• функция FILTER фильтрует измерения dimcustomer по значению столбца customertype, в данном случае равному Person - т.е. фильтрует только физических лиц.

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

Аналогичным образом создается мера Прибыль для юр. лиц с тем отличием, что вместо fact[discountamount] мы будем использовать fact[bomus], а вместо IN {"Person"} будем использовать IN {"Company"}:

Прибыль для юр. лиц = CALCULATE (SUMX (fact, fact[salesamount] - fact[bonus]), FILTER (dimcustomer, dimcustomer[customertype] IN {"Company"} ))

В результате мы можем, например, создать следующую диаграмму с использованием созданных мер:

При сложении двух формул, мы получим прибыль с учетом типа клиента:

CALCULATE (
    SUMX (fact, fact[salesamount] - fact[discountamount]), 
    FILTER (dimcustomer, dimcustomer[customertype] IN {"Person"} )
)
    + CALCULATE (
        SUMX (fact, fact[salesamount] - fact[bonus]), 
        FILTER (dimcustomer, dimcustomer[customertype] IN {"Company"} )
)

Пример 4. Расчет суммы продаж за указанный период (несколько месяцев)

В этом примере мы попробуем понять, сколько выручки приносит “целевой“ период.

Используемые данные:

Для получения необходимых расчетов мы будем использовать следующие формулы:

Расчет количества продаж за указанный период:

CALCULATE (
COUNT (fact[onlinesaleskey]),
FILTER (
    dimdate,
    AND (dimdate[calendarmonth] >=
    202207, dimdate[calendarmonth] < 202209)
    )
)

где:

COUNT (fact[onlinesaleskey]) – подсчитывает количество транзакций в столбце onlinesaleskey таблицы фактов fact.

FILTER – фильтрует таблицу dimdate по определенному условию, которое состоит из двух частей:

• dimdate[calendarmonth] >= 202207 - выбираются только те строки таблицы dimdate, у которых значение в столбце calendarmonth больше или равно 202207 (июль 2022 года);

• dimdate[calendarmonth] < 202209 - выбираются только те строки, у которых значение в столбце calendarmonth меньше 202209 (сентябрь 2022 года).

Таким образом, фильтр выбирает только те строки из таблицы, которые соответствуют периоду с июля по сентябрь 2022 года.

Расчет суммы продаж за указанный период:

Аналогичным образом мы проведем расчет суммы продаж (с небольшими изменениями):

CALCULATE (
SUM (fact[salesamount]),
FILTER (
    dimdate,
    AND (dimdate[calendarmonth] >=
    202207, dimdate[calendarmonth] < 202209)
    )
)

где:

SUM (fact[salesamount]) – вычисляет сумму значений из столбца salesamount таблицы fact.

FILTER – как показано в примере выше, выбирает только те строки из таблицы, которые соответствуют периоду с июля по сентябрь 2022 года.

В результате выполнения этой формулы будет получена сумма продаж за определенный период времени (с июля по сентябрь 2022 года).

В качестве итога, используя созданные меры, мы может визуализировать полученные результаты следующим образом:

Пример 5. Расчет динамики продаж за определенный период

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

Используемые данные:

Расчет динамики продаж по месяцам

Для расчета динамики продаж за два месяца (в нашем примере август-сентябрь), мы сделаем следующее:

1. Сперва мы рассчитаем объем продаж за август. Для этого мы создадим меру с использованием следующей формулы:

   CALCULATE (
   SUM (fact[salesamount]),
   FILTER (dimdate, dimdate[calendarmonth] = 202208)
   )

   где:
   SUM (fact[salesamount]) – вычисляет сумму значений из столбца salesamount таблицы fact;
   FILTER – выбираются только те строки таблицы dimdate, у которых значение в столбце calendarmonth равно 202208 (август 2022 года);

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

   CALCULATE (
   SUM (fact[salesamount]),
   FILTER (dimdate, dimdate[calendarmonth] = 202209)
   )

   где 202209 – сентябрь 2022 года.

3. Теперь самое время получить разницу между двумя этими результатами, чтобы определить динамику продаж. Для этого мы вычтем значения, полученные за август из значений, полученных за сентябрь:

   CALCULATE (
   SUM (fact[salesamount]),
   FILTER (dimdate, dimdate[calendarmonth] = 202209)
   )
   - CALCULATE (
   SUM (fact[salesamount]),
   FILTER (dimdate, dimdate[calendarmonth] = 202208)
   )

   Результат можно визуализировать, например, с помощью таблицы или диаграммы:
   

   

   

Расчет динамики продаж по годам

Динамика продаж по годам рассчитывается аналогичным образом – нам необходимо только заменить месяцы на года и использовать соответствующий столбец (calendaryear), где прописаны года:

CALCULATE (
SUM (fact[salesamount]),
FILTER (dimdate, dimdate[calendaryear] = 2022)
)
- CALCULATE (
SUM (fact[salesamount]),
FILTER (dimdate, dimdate[calendaryear] = 2021)
)

где

FILTER (dimdate, dimdate[calendaryear] = 2022) и FILTER (dimdate, dimdate[calendaryear] = 2021) фильтруют данные по годам.

Созданную меру можно добавить в таблицу для полноты картины:

Чтобы иметь быстрый доступ к полученной статистике, можно создать дашборд:

Пример 6. Расчет прибыли по каждой продаже за месяц с учетом дополнительных платежей

Рассмотрим пример, в котором прибыль рассчитывается по каждой продаже с учетом различных платежей (например, за аренду помещений). Дополнительные платежи оплачиваются в разное время и не являются заказами. Рассматриваемый период - сентябрь 2022 года.

Используемые данные:

Для проведения расчетов мы воспользуемся следующей формулой:

CALCULATE (
	SUMX (fact, fact[salesamount] - fact[discountamount]),
    FILTER (fact, fact[transactiontype] IN {“order”})
)
- CALCULATE (
	SUM (fact[payment]),
    REMOVEFILTERS (fact),
    FILTER (fact, fact[transactiontype] IN {“payment”}),
    FILTER (dimdate, dimdate[calendarmonth] = 202209)
)
/ CALCULATE (
	COUNT (fact[onlinesaleskey]),
	REMOVEFILTERS (fact),
	FILTER (fact, fact[transactiontype] IN {“order”}),
	FILTER (dimdate, dimdate[calendarmonth] = 202209)
)

Первая часть формулы суммирует выручку от каждой продажи:

SUMX (fact, fact[salesamount]SUMX (fact, fact[salesamount]

и вычитает сумму скидки:

- fact[discountamount])

Далее применяется фильтр, чтобы учитывать только транзакции типа “order" в столбце transactiontype таблицы fact.

Вторая и третья части рассчитывают дополнительные платежи по каждому заказу:

Во второй части формулы используется CALCULATE-функция, которая вычисляет сумму всех платежей за сентябрь 2022 года, относящихся к продажам за этот же период. Функция REMOVEFILTERS используется для того, чтобы убрать все фильтры из таблицы fact. Функция FILTER добавляет фильтры по типу транзакции и месяцу календаря.

В третьей части CALCULATE-функция подсчитывает количество продаж онлайн за сентябрь 2022 года. Функция REMOVEFILTERS убирает все фильтры из таблицы "fact", функция и FILTER добавляет фильтры по типу транзакции и месяцу календаря.

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

Пример 7. Расчет суммы накопленного итога

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

Используемые данные

Для проведения расчетов, воспользуемся следующей формулой:

CALCULATE (
            SUM ( 'facts'[salesamount] ),
            FILTER (
                ALL ( 'dimdate' ),
                AND( 'dimdate'[datekey] >= DATE ( YEAR(MAX('dimdate'[datekey])), 1, 1 ),

                  'dimdate'[datekey] <= MAXX ( 'dimdate', 'dimdate'[datekey] 
                ))
            )
        )

Данная формула легла в основу новой меры – Накопленный итог, и с ее помощью мы визуализировали накопленный итог со сбросом по годам:

На изображении видно, что начиная с первого месяцам 2009 года накопление сбрасывается и происходит дальнейший накопительный расчет.

Накопленный итог без сброса по годам пока не работает.

Пример 8. Вычисление средней цены товара для каждого чека

В данном примере мы вычислим среднюю цену товара для каждого чека, используя данные о ценах и номерах заказов из таблицы factonlinesales.

Используемые данные:

Для вычислений мы воспользуемся следующей формулой:

Средняя цена товара по чеку = DIVIDE(
            CALCULATE(
                SUM('factonlinesales'[unitprice]),
                REMOVEFILTERS('factonlinesales'),
                SUMMARIZE('factonlinesales', 'factonlinesales'[salesordernumber])
            ), 
            CALCULATE(
                COUNT('factonlinesales'[productkey]),
                REMOVEFILTERS('factonlinesales'),
                SUMMARIZE('factonlinesales', 'factonlinesales'[salesordernumber])
            ), BLANK()
        )

Данная формула состоит из нескольких шагов:

1. Вычисляется сумма цен всех товаров в чеках без учета фильтров (REMOVEFILTERS). Для этого сначала применяется функция CALCULATE, которая вычисляет сумму цен всех товаров по каждому уникальному номеру заказа (salesordernumber) из таблицы factonlinesales, затем функция REMOVEFILTERS удаляет фильтры, которые могут изменить сумму цен, и возвращает данные без фильтров.

2. Вычисляется количество товаров в каждом чеке без учета фильтров. Для этого также используется функция CALCULATE с аналогичными параметрами.

3. Вычисляется средняя цена товара по каждому чеку с помощью функции DIVIDE. Результат первого шага делится на результат второго шага, чтобы получить среднюю цену товара по каждому чеку.

4. Результат деления на ноль заменяется на пустую строку (BLANK()).

Пример 9. Расчет среднего чека

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

Используемые данные:

Для вычислений мы воспользуемся следующей формулой:

Средний чек = DIVIDE (
                CALCULATE (
                    SUMX (
                        'факты онлайн продаж',
                        'факты онлайн продаж'[unitprice] * 'факты онлайн продаж'[salesquantity]
                    ),
                    REMOVEFILTERS ( 'факты онлайн продаж' ),
                    SUMMARIZE (
                        'факты онлайн продаж',
                        'факты онлайн продаж'[salesordernumber]
                    )
                ),
                CALCULATE (
                    DISTINCTCOUNT ( 'факты онлайн продаж'[salesordernumber] ),
                    REMOVEFILTERS ( 'факты онлайн продаж' ),
                    SUMMARIZE (
                        'факты онлайн продаж',
                        'факты онлайн продаж'[salesordernumber]
                    )
                )

Данная формула сначала вычисляет сумму произведений цены за единицу товара (unitprice) и количества проданных единиц (salesquantity) для всех строк таблицы факты онлайн продаж. Затем она удаляет фильтры (если они есть), примененные к таблице, и суммирует количество номеров заказов (salesordernumber).

После этого происходит деление на результат второго CALCULATE, который считает количество уникальных номеров заказов (с помощью DISTINCTCOUNT), исключая фильтры, и также суммирует номера заказов. Это и дает среднюю цену проданных товаров.

Пример 10. Расчет меры по условию с проверкой на уровне строк и учетом коэффициента

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

Используемые данные:

Формула 1

Формула ниже суммирует значения в столбце salesamount таблицы факты онлайн продаж, но с одним условием: если значение идентификатора продукта (productkey) равно 6, то значение salesamount умножается на 0.8 (что даёт 80% от исходного значения). Если условие не выполняется, то используется исходное значение salesamount. Это повторяется для каждой строки в таблице факты онлайн продаж.

Таким образом, формула дает суммарное значение продаж, но продажи для продукта с идентификатором 6 учитываются с коэффициентом 0.8.

Объем продаж с коэффициентом = sumx (
                                 'факты онлайн продаж',
                                  if (
                                   'факты онлайн продаж'[productkey]= 6,
                                   'факты онлайн продаж'[salesamount]* 0.8,
                                   'факты онлайн продаж'[salesamount]
                                   )
                                )

Формула 2

Формула, приведенная ниже, суммирует значения столбца salesamount, но только для тех строк, где значение идентификатора продукта отлично от 6. Если условие не выполняется (т.е. если идентификатором продукта является 6), то вместо значения salesamount используется 0. Результатом является сумма значений из столбца salesamount для всех строк, где productkey отличен от 6. Если в строке productkey равен 6, то значение salesamount в сумму не включается.

Объем продаж без учета отдельного товара = sumx(
                                     'факты онлайн продаж', 
                                        if (
                                        'факты онлайн продаж'[productkey] <> 6,
                                        'факты онлайн продаж'[salesamount],
                                        0
                                        )
                                      )   

Смотрите также

📄 Поддерживаемые функции DAX
📄 Создание мер