Zákazník měsíce v jazyku DAX

V Power BI nebo v  Excelu můžeme relativně jednoduše vytvořit report, který bude zobrazovat nákupy zákazníků v aktuálním měsíci. V takto vytvořeném reportu není problém identifikovat zákazníka s největším objemem nákupu ve vybraném období, a to bez nutnosti napsat jediný řádek DAX kódu. Narazit však můžeme na přehlednost takto vytvořeného reportu, a to především v situaci, kdy obsluhujeme mnoho zákazníků, nebo pokud chceme současně zobrazit nejlepší zákazníky nejen v aktuálním měsíci, ale i měsících minulých. Pokud bychom se chtěli při identifikaci nejlepších zákazníků ve více měsících vyhnout psaní DAX kódu, museli bychom vytvořit report, který by obsahoval jak jednotlivé měsíce, tak i zákazníky, a to ideálně v jednom vizuálu, abychom pracovali se správnou granularitou. Takto vytvořený vizuál by však ztratil svůj význam již při relativně malém počtu zákazníků, v kombinaci s více zobrazenými měsíci.  Snadno si můžeme představit například vizuál Matrix s tisíci zákazníky v řádcích a několika měsíci ve sloupcích. Identifikovat v takto vytvořeném vizuálu nejlepší zákazníky v jednotlivých měsících již prakticky není možné. V jazyku DAX si naštěstí můžeme granularitu dat pro výpočet vytvořit programově a můžeme tak jednoduše přizpůsobit výpočet požadovanému výstupu. Můžeme si například v řádcích tabulky zobrazovat pouze měsíce, a v hodnotách nejlepší zákazníky v daném měsíci, bez nutnosti zobrazovat všechny ostatní zákazníky, kteří jsou v aktuálním měsíci na pomyslném druhém a dalších místech. V řádcích s roky si pak můžeme zobrazit nejlepšího zákazníka nebo více zákazníků podle nákupů za celý rok, nebo nejlepšího zákazníka, který měl nejvyšší měsíční nákup ve kterémkoliv měsíci v daném roce, a tak dále. V následující části jsou zobrazeny různé varianty těchto výpočtů. Power BI soubor s řešením je dostupný ke stažení níže pod tímto příspěvkem.

Identifikace nejlepších zákazníků v Power BI

Příklady v tomto příspěvku jsou vytvořeny ve cvičném Power BI souboru Adventure Works DW 2020.pbix. Tento soubor je navíc rozšířen o dvě nové tabulky se záznamy o prodejích produktů. V první tabulce jsou pouze prodeje přes kamenné prodejny - tabulka 'Reseller Sales', ve druhé tabulce jsou pouze záznamy o prodejích přes internetové obchody - tabulka 'Internet Sales'. 

V prvním kroku si připravíme vizuály pro následující výpočty. Na stránku v Power BI souboru si vložíme vizuál Matrix s roky a měsíci v řádcích, a se sumou prodejů z tabulky 'Internet Sales'. V průřezu je ještě pro lepší přehlednost vybrán pouze jeden rok. Výchozí report tak může vypadat následovně.

Nyní můžeme přejít k samotným výpočtům. Následující měřítko bude zatím vracet pouze sumu, kterou utratil nejlepší zákazník v daném měsíci za nákupy produktů přes internet.

Měřítko:

Nejvyšší útrata zákazníka (ve vybraném období) =

VAR ZakazniciNakupy =

    ADDCOLUMNS

    (

        VALUES(Customer[Zákazník]),

        "@Nákup",

        [Internetové prodeje]

    )

VAR Vysledek =

    MAXX

    (

        ZakazniciNakupy,

        [@Nákup]

    )

RETURN  

    Vysledek

V první proměnné s názvem ZakazniciNakupy je uložena tabulka, která obsahuje dva sloupce – zákazníky a částky utracené za jejich nákupy. Ve druhé proměnné s názvem Vysledek pak pomocí funkce MAXX() vybereme nejvyšší hodnotu ze sloupce [@Nákup], který byl vytvořený v dočasné  tabulce v proměnné ZakazniciNakupy. Tato hodnota je výsledkem měřítka a představuje nejvyšší částku za nákupy jednoho zákazníka, které byly uskutečněny v  období, ve kterém je měřítko vyhodnoceno.

V lednu 2020 zákazník s nejvyšší útratou zakoupil produkty za 4 484,96 Kč. V únoru 2020 utratil některý ze zákazníků 4 800,01 Kč,  a tak dále. V řádu souhrnů, stejně jako v řádku s rokem 2020, můžeme vidět hodnotu 6 212,4 Kč. Jedná se o nejvyšší útratu jednoho zákazníka za celý rok 2020. Hodnota v řádku souhrnů je shodná s hodnotou v řádku s rokem 2020, protože v průřezu je vybraný právě pouze rok 2020. Pokud bychom chtěli vidět v řádku s roky nejvyšší měsíční útratu jednoho zákazníka, musíme změnit kontext, ve kterém je měřítko [Internetové prodeje] vyhodnoceno.

Měřítko:

Nejvyšší útrata zákazníka (měsíční) =

VAR ZakazniciNakupy =

    ADDCOLUMNS

    (

        SUMMARIZE(Sales, Customer[Zákazník], 'Date'[Měsíc rok]),

        "@Nákup",

        [Internetové prodeje]

    )

VAR Vysledek =

    MAXX

    (

        ZakazniciNakupy,

        [@Nákup]

    )

RETURN  

    Vysledek

V nové verzi výpočtu má nyní tabulka v proměnné ZakazniciNakupy tři sloupce – jména zákazníků, měsíce a hodnotu nákupů zákazníka v daném měsíci. Pokud novou verzi měřítka vložíme do původního vizuálu, v řádcích s měsíci bude výsledek stejný jako původní výpočet, v řádku souhrnů a v řádku s rokem 2020 již však bude hodnota jiná.

Jak můžeme vidět na obrázku výše, nové měřítko vrací v řádcích s měsíci stejné hodnoty jako předchozí výpočet. V řádku s rokem 2020 a v řádku souhrnů je již hodnota jiná, a nově odpovídá nejvyšší měsíční útratě jednoho zákazníka v daném roce. Jedná se o zákazníka, který utratil 4 848,96 Kč, a tato nejvyšší útrata proběhla v lednu 2020.  Obě předchozí měřítka vrací pouze částku, kterou nejlepší zákazník utratil v daném období. Následující měřítko již bude vracet také jméno nejlepšího zákazníka.

Měřítko:

Nejlepší zákazník (ve vybraném období) =

IF

(

    [Internetové prodeje] > 0,

    VAR ZakazniciNakupy =

        ADDCOLUMNS

        (

            VALUES(Customer[Zákazník]),

            "@Nákup",

            [Internetové prodeje]

        )

    VAR NejlepsiZakaznik =

        SELECTCOLUMNS

        (

            TOPN

            (

                1,

                ZakazniciNakupy,

                [@Nákup],

                DESC

            ),

            "@Zakaznik a nakup",

            [Zákazník] & " - " & [@Nákup] & " Kč"

        )

    VAR Vysledek =

        IF

        (

            COUNTROWS(NejlepsiZakaznik) = 1,

            NejlepsiZakaznik,

            "Více zákazníků se stejnou útratou"

        )

    RETURN

        Vysledek

)

Logika výpočtu je velmi podobná jako v předchozích příkladech. Hlavní rozdíl je možné vidět v proměnné NejlepsiZakaznik. Nově již nemůžeme použít funkci MAXX(), ale pro výběr nejlepšího zákazníka je použita funkce TOPN(), protože již nepracujeme pouze s číslem, ale také s textovým řetězcem. Výsledkem měřítka musí být jedna skalární hodnota, a proto je ještě v proměnné NejlepsiZakaznik použita funkce SELECTCOLUMNS(), ve které z tabulky se dvěma sloupci vytvoříme tabulku pouze s jedním sloupcem, který obsahuje textový řetězec se jménem zákazníka a částkou. V proměnné Vysledek ještě musíme ověřit, zda  je v tabulce NejlepsiZakaznik pouze jeden řádek. Více řádků by v této dočasné tabulce bylo v případě, kdy by na prvním místě bylo více zákazníků se stejnou útratou v daném období. Měřítko si opět můžeme vložit do připraveného vizuálu a podívat se na výsledek.

Jak je možné vidět na obrázku výše, nové měřítko již vrací také jméno zákazníka. V závorce za jménem každého zákazníka je jedinečný identifikátor zákazníka, abychom se vyhnuli sloučení více zákazníků se stejným jménem do jednoho řádku. Měřítko [Nejlepší zákazník (ve vybraném období)] vrací nejlepšího zákazníka v období, ve kterém je měřítko vyhodnoceno. Toto měřítko můžeme použít také v jiném kontextu, například v kontextu kategorií produktů, v kontextu jednotlivých obchodů, a tak dále. Stejně jako při zobrazení nejvyšší částky utracené jedním zákazníkem můžeme i nyní změnit granularitu výpočtu tak, abychom v řádku souhrnů viděli nejlepšího zákazníka podle měsíčních nákupů.

Měřítko:

Zákazník měsíce =

IF

(

    [Internetové prodeje] > 0,

    VAR ZakazniciNakupy =

        ADDCOLUMNS

        (

            SUMMARIZE(Sales, Customer[Zákazník], 'Date'[Měsíc rok]),

            "@Nákup",

            [Internetové prodeje]

        )

    VAR NejlepsiZakaznik =

        SELECTCOLUMNS

        (

            TOPN

            (

                1,

                ZakazniciNakupy,

                [@Nákup],

                DESC

            ),

            "@Zakaznik a nakup",

            [Zákazník] & " " & [@Nákup] & " Kč"

        )

    VAR Vysledek =

        IF

        (

            COUNTROWS(NejlepsiZakaznik) = 1,

            NejlepsiZakaznik,

            "Více zákazníků se stejnou útratou"

        )

    RETURN

        Vysledek

)

Pokud si nové měřítko [Zákazník měsíce] porovnáme s měřítkem [Nejlepší zákazník (ve vybraném období)], rozdíl bude opět pouze v řádcích s roky nebo v řádku souhrnů.

Pokud odebereme průřez s filtrem roků, a ve vizuálu Matrix ponecháme pouze měřítko [Zákazník měsíce], můžeme vidět co se stane, pokud je v jednom měsíci více zákazníků se stejnou útratou.

V řádcích s měsíci je výsledkem měřítka [Zákazník měsíce] jméno nejlepšího zákazníka v daném měsíci. V řádku s rokem 2020 můžeme vidět, že nejvyšší měsíční nákup v roce 2020 uskutečnil zákazník Ricky Navarro. V řádku souhrnů je pak zákazník Larry Vazquez, který měl nejvyšší měsíční nákup napříč všemi měsíci. Tato rekordní útrata proběhla v listopadu 2019. V dubnu 2019 a v červenci 2019 bylo na prvním místě více zákazníků se stejnou útratou. Tato situace by byla v reálném modelu nad reálnými daty málo pravděpodobná. Nicméně může nastat a proto si ještě můžeme ukázat výpočet, který vrátí všechny zákazníky na prvním místě, pokud je zákazníků na prvním místě více.

Měřítko:

Zákazník měsíce 2 =

IF

(

    [Internetové prodeje] > 0,

    VAR ZakazniciNakupy =

        ADDCOLUMNS

        (

            SUMMARIZE(Sales, Customer[Zákazník], 'Date'[Měsíc rok]),

            "@Nákup",

            [Internetové prodeje]

        )

    VAR NejlepsiZakaznik =

        SELECTCOLUMNS

        (

            TOPN

            (

                1,

                ZakazniciNakupy,

                [@Nákup],

                DESC

            ),

            "@Zakaznik a nakup",

            [Zákazník] & " " & [@Nákup] & " Kč"

        )

    VAR Vysledek =

        CONCATENATEX

        (

            NejlepsiZakaznik,

            [@Zakaznik a nakup],

            UNICHAR(10)

        )

    RETURN

        Vysledek

)

Výsledek měřítka [Zákazník měsíce 2] můžeme vidět na následujícím obrázku.

Měřítko [Zákazník měsíce 2] nyní vrací v případě shody na prvním místě  všechny zákazníky, kteří se dělí o pomyslné první místo, z pohledu měsíčních nákupů. Tuto situaci můžeme vidět například v řádku s měsícem červenec 2019. 

Logika výpočtů uvedených v tomto příspěvku může být s drobnými úpravami použita také k vyhodnocení například nejlepších prodejců, nejlepších produktů nebo nejlepších obchodů v určitém období a podle vybraných kritérií, v závislosti na požadavcích uživatelů reportu. Další příklady jsou dostupné na stránce DAX – příklady.

Stáhnout soubor s řešením.
č. 65