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

• Определение новых, постоянных и потерянных клиентов
• Модель данных для решения задачи на практике
• Разбор формулы расчета количества текущих клиентов
• Разбор формулы расчета количества новых клиентов
• Разбор формулы расчета количества постоянных клиентов
• Разбор формулы расчета количества потерянных клиентов
• Подробное ВИДЕО «Расчет количества новых, постоянных и потерянных клиентов в Power BI (Power Pivot) на языке DAX»

---

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы на практическом примере разберем несколько DAX формул, которые позволят Вам в Power BI и Power Pivot рассчитывать количество новых, постоянных и потерянных клиентов.
 

В данной формуле нам понадобятся следующие DAX функции: SUMMARIZE, SUMX, PARALLELPERIOD, COUNTROWS, CALCULATE и CALCULATETABLE, DISTINCTCOUNT, переменные DAX (VAR + RETURN), а также для правильного отображения итогов конструкция из функций IF и HASONEVALUE.

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

А также, скачайте файлы к данной статье (для Power BI и Power Pivot)

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

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

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

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

 

Определение новых, постоянных и потерянных клиентов

Итак, прежде чем решать данную задачу в Power BI (Power Pivot), нам нужно определиться — собственно, а кто такие новые, кто такие постоянные, и кто такие потерянные клиенты. Когда мы ответим на этот вопрос, нам уже гораздо легче будет подойти к решению данной задачи в DAX.

Когда мы строим данный отчет, то в любом случае в этом отчете будут использоваться два периода: текущий период и какой-то прошедший период.

И если отвечать на вопрос: «Кто такие новые клиенты?», то можно дать такой ответ – это те клиенты, которые производили покупку в текущем периоде и в то же время, в прошлом периоде покупок у них не было. То есть, новые клиенты должны удовлетворять условию: покупки в текущем периоде > 0 и покупки в прошлом периоде = 0.

Хорошо, разобрались, идем далее, кто такие постоянные клиенты – это те клиенты, которые имеют покупки как в текущем, так и в предыдущем периоде. То есть, постоянные клиенты должны удовлетворять условию: покупки в текущем периоде > 0 и покупки в прошлом периоде также > 0.

Ну и потерянные клиенты – это обратная ситуация новых клиентов. То есть, это те клиенты, которые имели покупки в предыдущем периоде, а вот в текущем периоде уже нет.
 

Модель данных для решения задачи на практике

Теперь давайте перейдем к созданию самих DAX формул в Power BI (Power Pivot), рассчитывающих количество новых, постоянных и потерянных клиентов.
Но, прежде, давайте посмотрим на исходную модель данных:

 
В модели данных Power BI у меня имеется 2 таблицы.

Справочник Календарь:

 
И таблица фактов по продажам:

 

Где, имя клиента означает одного и того же клиента, то есть, например, клиент Петр с датой покупки 10 марта 2018 – это тот же самый клиент Петр, который произвел покупку 10 мая 2019.
 

Разбор формулы расчета количества текущих клиентов

Первое, с чего нужно начать, это, собственно, просто рассчитать количество текущих клиентов. Делается это очень просто:

КолТекущихКлиентов =
DISTINCTCOUNT('Продажи'[Клиент])

Данная DAX формула достаточно простая и состоит всего лишь из одной функции DISTINCTCOUNT.

Эта функция подсчитывает количество уникальных значений в переданном столбце. А мы в эту функцию передаем столбец с клиентами.

Также, нужно помнить, что данная DAX функция реагирует на внешний пользовательский контекст фильтра, который у нас поступает из визуализации в Power BI (2018 и 2019 года, соответственно).

И эти года фильтруют столбец с клиентами, который встроен во внутрь DISTINCTCOUNT, а значит данная функция будет подсчитывать количество клиентов именно по текущему году.

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

 

Разбор формулы расчета количества новых клиентов

Теперь переходим к самому важному – к разбору DAX формулы с помощью которой мы сможем рассчитать количество новых клиентов в Power BI (Power Pivot).

Итак, в самом начале данной статьи мы определились, кто такие новые клиенты — это те клиенты, которые имеют покупку в текущем периоде и при этом не имеют покупок в предыдущем периоде.

То есть, вся задача сводится к следующему:

• нам нужно выявить клиентов для текущего периода
• взять каждого клиента из текущего периода и проверить наличие у него покупок в предыдущем периоде

И если покупки у этого клиента в предыдущем периоде имеются, то нас этот клиент не устраивает, так как по нашему определению новый клиент не должен иметь покупок в предыдущем периоде.

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

Поможет нам в этом так называемая итерационная X-функция SUMX. Данная DAX функция выполняет выражение, указанное во втором параметре, по каждой строке таблицы, указанной в первом параметре. А что такое «по каждой строке»?

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

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

Хорошо, но как это все поможет нам в расчете количества новых клиентов?

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

А в этом выражении мы, во-первых, рассчитаем по этому выбранному клиенту его покупки в предыдущем периоде, а во-вторых, создадим условие, по которому если у перебираемого клиента есть покупки в предыдущем периоде, то условие возвратит 0, е если покупок нет, то возвратит 1.

Ну и на последней своей операции SUMX произведет сумму всех получившихся для каждого текущего клиента нулей (0) и единиц (1). И результат этой суммы будет являться ничем иным, как количеством новых клиентов, которое мы рассчитаем в Power BI (Power Pivot).

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

КолНовыхКлиентов (Текущий год к Прошлому году) = 
-- расчет количества
VAR kolichestvo = 
SUMX(
    SUMMARIZE(
        'Продажи';
        'Продажи'[Клиент]
    );
    IF(
        CALCULATE(COUNTROWS('Продажи');PARALLELPERIOD('спрКалендарь'[Date];-1;YEAR))=0;
        1;
        0
    )
)
RETURN

-- убираем данные из общего итога
IF(
    HASONEVALUE('спрКалендарь'[Date].[Год]);
    kolichestvo
)

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

 
А все, что остальное – DAX переменная (VAR RETURN) и конструкция IF – HASONEVALUE — это уже дополнительный код.

Переменная используется для удобства написания меры, а конструкция IF – HASONEVALUE – это просто-напросто дополнительное «украшение» нашей визуализации. А конкретнее, с помощью этой конструкции мы имеем возможность убрать показ итогов по новым клиентам, так как итог тут совершенно лишний.

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

Ну, и если в столбце с годом 0 значений или более 1 (что соответствует строке с итогами в визуализации), то HASONEVALUE возвращает FALSE и, как следствие, DAX функция IF выполняет свой третий параметр. Если же он не указан, как у нас, то IF возвращает пустое значение BLANK, которое выводится вместо нашей формулы в итогах визуализации.

Теперь переходим к разбору основного кода меры, который находится внутри SUMX:

 
Итак, смотрите, у функции SUMX два параметра.

Первый параметр – сводная таблица по клиентам, которую возвращает SUMMARIZE:

 
И второй параметр – условие, внутри которого рассчитываются покупки клиентов за предыдущий период:

 
Таблица в первом параметре SUMX будет возвращать нам текущих клиентов. Почему?

Потому что на нее будет действовать внешний контекст фильтра из визуализации. И, например, для строки визуализации с 2019 годом данная таблица будет возвращать именно клиентов за 2019 год.

Хорошо, с первым моментом мы разобрались – текущих клиентов мы получили. Теперь SUMX будет брать каждого текущего клиента в отдельности и по нему вычислять выражение, в котором у нас код условия, а также код расчета покупок в предыдущем периоде.

Переходим к этому кусочку кода – ко второму параметру SUMX:

 
Здесь у нас используется условие IF, которое возвращает 1, если текущий клиент не имеет покупок в предыдущем периоде, и 0, если имеет.

Сами расчеты покупок в предыдущем периоде у нас рассчитаны через CALCULATE, которая, во-первых, принимает значение клиента, переданное функцией SUMX из первого параметра, и накладывает этого клиента, как фильтр на таблицу «Продажи».

А во-вторых, CALCULATE накладывает еще один второй фильтр – даты за предыдущий период, которые возвращает DAX функция PARALLELPERIOD.

Таким образом, таблица «Продажи» в первом параметре CALCULATE фильтруется 2-мя фильтрами – текущим клиентом, и прошлым периодом. Соответственно, если текущий клиент покупал в прошлом периоде, то в таблице «Продажи» будут соответствующие строки. А если этих покупок нет – то и строк в таблице «Продажи» не будет.

И теперь дело за малым – просто посчитать количество строк в отфильтрованной таблице «Продажи». Это мы легко можем реализовать с помощью функции COUNTROWS.

Ну вот и все… Если COUNTROWS возвращает положительное число, а то есть, это соответствует тому, что текущий клиент имеет покупки в предыдущем периоде, то наше условие IF не выполняется, а значит IF возвращает 0.

Ну а если COUNTROWS возвращает 0, а то есть, это соответствует тому, что у текущего клиента нет покупок в предыдущем периоде, то наше условие IF выполняется, а значит IF возвращает 1.

И, в итоге, после того как SUMX переберет всех текущих клиентов из таблицы в первом параметре, она суммирует все получившиеся результаты, которые возвращала IF для каждого клиента (нули и единицы), и выдаст нам итоговый ответ – количество новых клиентов, которых мы смогли рассчитать в Power BI (Power Pivot).

Результат работы этой DAX формулы будет таким:

 

Разбор формулы расчета количества постоянных клиентов

Формула расчета количества постоянных клиентов полностью идентична формуле, которую мы разбирали выше (расчет новых клиентов), за исключением условия IF во втором параметре DAX функции SUMX.

Итак, код меры, рассчитывающий постоянных клиентов следующий:

КолПостоянныхКлиентов (Текущий + Прошлый Год) = 
-- расчет количества
VAR kolichestvo = 
SUMX(
    SUMMARIZE(
        'Продажи';
        'Продажи'[Клиент]
    );
    IF(
        CALCULATE(COUNTROWS('Продажи');PARALLELPERIOD('спрКалендарь'[Date];-1;YEAR))>0;
        1;
        0
    )
)
RETURN

-- убираем данные из общего итога
IF(
    HASONEVALUE('спрКалендарь'[Date].[Год]);
    kolichestvo
)

Данный код я уже подробно разбирать не буду, так как он очень хорошо разбирался выше, я лишь обращу внимание на различие – а именно на само условие IF:

 
Вспомним определение, что мы обсуждали в начале статьи о постоянных клиентах, а именно, постоянные клиенты, это те клиенты, которые покупали как в текущем периоде, так и в прошлом периоде.

Соответственно, наша задача сводится опять к двум пунктам:

• получить текущих клиентов
• проверить покупки у текущих клиентов в прошлом периоде

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

Далее, во втором параметре SUMX мы по каждому перебираемому текущему клиенту проверяем продажи в прошлом периоде. Ну и если продажи там есть, а то есть, если COUNTROWS возвращает какое-то положительное число строк в таблице «Продажи», отфильтрованной двумя фильтрами – текущим клиентом и прошлым периодом, то SUMX подсчитает этого клиента (функция IF возвратит 1).

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

То есть, в итоге, вся разница формулы расчета постоянных клиентов от формулы новых клиентов – это знак условия IF. В прошлой формуле условие было «=0», то есть 0 строк в таблице «Продажи», а в текущей формуле условие «>0», то есть в таблице «Продажи» есть строки с покупками текущего клиента.

Результат выполнения данной DAX формулы в Power BI (Power Pivot) будет таким:

 

Разбор формулы расчета количества потерянных клиентов

Формула расчета количества потерянных клиентов в Power BI (Power Pivot) абсолютно противоположна формуле расчета новых клиентов. Вспомним определение – потерянные клиенты, это те клиенты, которые произвели покупку в предыдущем периоде, но в текущем периоде покупок у них нет.

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

Итак, DAX формула расчета количества потерянных клиентов будет такая:

КолПотерянныхКлиентов (В Текущем Году от Прошлого Года) = 
-- расчет количества
VAR kolichestvo = 
SUMX(
    CALCULATETABLE(
        SUMMARIZE(
            'Продажи';
            'Продажи'[Клиент]
        );
        PARALLELPERIOD('спрКалендарь'[Date];-1;YEAR)
    );
    IF(
        (CALCULATE(COUNTROWS('Продажи')))=0;
        1;
        0
    )
)
RETURN
-- убираем данные из общего итога
IF(
    HASONEVALUE('спрКалендарь'[Date].[Год]);
    kolichestvo
)

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

Итак, в первом параметре SUMX мы перебираем всех клиентов из прошлого периода:

 
DAX функция SUMMARIZE возвращает нам таблицу, содержащую один столбец с клиентами. Но, SUMMARIZE обернута в CALCULATETABLE, которая заменяет все внешние пользовательские фильтры (в нашем случае фильтр по году из визуализации) на свой внутренний контекст фильтра, равный прошлому периоду (данный фильтр устанавливает PARALLELPERIOD).

И, как итог, в первом параметре SUMX возвращается таблица с клиентами из прошлого периода.

Далее SUMX по каждому клиенту из прошлого периода выполняет выражение, указанное во втором параметре. В данном выражении, внутри условия IF при помощи функции COUNTROWS мы рассчитываем количество строк в таблице «Продажи». Эта таблица будет отфильтрована перебираемым клиентом из прошлого периода (данный контекст строки функция CALCULATE превратит в контекст фильтра), а также текущим периодом (фильтр по году из визуализации).

Если количество строк в таблице «Продажи» будет равно 0, то такого клиента мы посчитаем (функция IF возвратит 1), так как именно он нам и нужен. И если количество строк будет положительным, то считать такого клиента уже не будем.

В итоге получим следующий результат:

 

Подробное ВИДЕО: «Расчет количества новых, постоянных и потерянных клиентов в Power BI (Power Pivot) на языке DAX»

Ссылки из видео:
1) [Скачивайте] Файлы к уроку для Power BI и Power Pivot: скачать
2) [Скачивайте PDF] Справочник DAX функций для Power BI и Power Pivot на русском языке: скачать

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

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

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

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


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

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

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

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