Сравнение версий

Ключ

  • Эта строка добавлена.
  • Эта строка удалена.
  • Изменено форматирование.
Примечание

Перед просмотром примеров, изучите процедуру создания мер.

Информация

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

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

  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. Расчет доли продаж по продуктам внутри бренда
Якорь
ex2
ex2

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

    Блок кода
    Доли по брендам =
    SUM ( factonlinesales[salesamount] )
        / CALCULATE ( SUM ( factonlinesales[salesamount] ), REMOVEFILTERS ( dimproduct ) )

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

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

    Блок кода
    Доля в бренде =
    SUM ( factonlinesales[salesamount] )
        / CALCULATE (
            SUM ( factonlinesales[salesamount] ),
            REMOVEFILTERS ( dimproduct ),
            SUMMARIZE ( dimproduct, dimproduct[brandname] )
        )

Выборка
hiddentrue

Пример 3. Расчет средневзвешенной цены

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

  1. Произвести расчет общей стоимости продаж:
    SumVolume = sumx(OrdersFacts, OrdersFacts[Quantity]*OrdersFacts[ActualUnitPrice])
    где sumx умножает количество товара Quantity на фактическую стоимость каждого товара ActualUnitPrice.

  2. Произвести расчет общего объема продаж (шт):
    SumQty = sum (OrdersFacts[Quantity])
    где sum суммирует все значения в столбце Quantity таблицы OrdersFacts.

  3. Произвести расчет средневзвешенной цены:
    AverageSum = DIVIDE([SumVolume],[SumQty ])

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

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

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

Таблица фактов

Таблица измерений

fact
Используемые столбцы:

  • salesamount – сумма, которую заплатил клиент с учетом количества;

  • discountamount – сумма скидки;

  • bonus – бонус (отдельный для каждого клиента).

dimcustomer
Используемый столбец:

  • customertype – тип клиента (“Person“ – физическое лицо, “Company“ – юридическое).

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

Блок кода
Прибыль для физ лиц =
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. Расчет суммы продаж за указанный период (несколько месяцев)
Якорь
ex4
ex4

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

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

Таблица фактов

Таблица измерений

fact
Используемые столбцы:

  • salesamount – сумма, которую заплатил клиент с учетом количества;

  • onlinesaleskey – уникальный идентификатор заказа (транзакции);

dimdate
Используемый столбец:

  • calendarmonth – год + месяц.

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

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

Блок кода
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. Расчет динамики продаж за определенный период
Якорь
ex5
ex5

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

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

Таблица фактов

Таблица измерений

fact
Используемые столбцы:

  • salesamount – сумма, которую заплатил клиент с учетом количества;

dimdate
Используемый столбец:

  • calendarmonth – год + месяц;

  • calendaryear – год.

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

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

  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. Расчет прибыли по каждой продаже за месяц с учетом дополнительных платежей
Якорь
ex6
ex6

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

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

Таблица фактов

Таблица измерений

fact
Используемые столбцы:

  • salesamount – сумма, которую заплатил клиент с учетом количества;

  • onlinesaleskey – уникальный идентификатор заказа;

  • transactiontype – тип транзакции (order или payment)

dimdate
Используемый столбец:

  • calendarmonth – год + месяц;

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

Блок кода
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. Расчет суммы накопленного итога
Якорь
ex7
ex7

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

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

Таблица фактов

Таблица измерений

facts
Используемый столбец:

  • salesamount – сумма, которую заплатил клиент с учетом количества;

dimdate
Используемый столбец:

  • calendarmonth – номера месяцев с указанием года. Например: 200801, где 2008 - годdatekey – содержит уникальные даты, которые используются для группировки и суммирования данных о продажах в контексте заданного периода времени.

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

Блок кода
CALCULATE (
            SUM ( 'facts'[salesamount] ),
            FILTER (
                ALL ( 'dimdate' ),
                AND( 'dimdate'[datekey] >= DATE ( YEAR(MAX('dimdate'[datekey])), 1, 1 ),

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

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

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

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

Примечание

Данная формула функционирует в бета-режиме. Рекомендуется тестирование на небольшом объеме данных.

панель
panelIconIdatlassian-warning
panelIcon:warning:
bgColor#FFEBE6

Ограничения, касающиеся данной формулы:

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

  • Не поддерживается фильтрация накопленного итога,  а также нет возможности задать минимальную/максимальную дату для расчета накопления.

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

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

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

Таблица фактов

factonlinesales
Используемые столбцы:

  • unitprice – цена за единицу товара;

  • salesordernumber – номер заказа;

  • productkey – уникальный идентификатор товара.

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

Блок кода
Средняя цена товара по чеку = 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. Расчет среднего чека
Якорь
ex9
ex9

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

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

Таблица фактов

факты онлайн продаж
Используемые столбцы:

  • unitprice – цена за единицу товара;

  • salesquantity – количество проданного товара;

  • salesordernumber – номер заказа.

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

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

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

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

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

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

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

Таблица фактов

факты онлайн продаж
Используемые столбцы:

  • productkey – уникальный идентификатор товара;

  • salesamount – сумма, которую заплатили клиенты с учетом количества.

Формула 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
                                        )
                                      )   

Пример 11. Расчет скидки с учётом промоакции
Якорь
ex11
ex11

Платформа поддерживает создание вложенных условия с помощью нескольких функций IF. Ниже приведен пример использования вложенной функции IF внутри функции SUMX. Данная формула суммирует продажи по всем строкам таблицы factonlinesales. Если promotionkey равно 6, то salesamount умножается на 0,8. Если promotionkey равно 7, то salesamount умножается на 0,7. Если ни одно из этих условий не выполняется, то salesamount остается неизменным. Иными словами, если промоакция имеет код 6, то применяется скидка 20%; если промоакция имеет код 7, то применяется скидка 30%. В противном случае, никаких скидок не применяется.

Блок кода
Скидки = SUMX(factonlinesales, 
              IF(factonlinesales[promotionkey]= 6, factonlinesales[salesamount]* 0.8,
              IF( factonlinesales[promotionkey]= 7 , factonlinesales[salesamount]* 0.7, factonlinesales[salesamount])
              )
            )

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

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