Noví a vracející se zákazníci v Power BI a DAX

Zjištění počtu nových a vracejících se zákazníků je relativně častý požadavek uživatelů Power BI reportů. Jedná se ale o komplexní výpočty, které mohou být velmi náročné na výpočetní výkon, a u kterých bychom měli klást velký důraz na přesné zadání. V tomto příspěvku si ukážeme dva z více možných přístupů k určení nových a vracejících se zákazníků. 

V prvním případě budeme určovat nové a vracející se zákazníky relativně s ohledem na vnější filtry. Ve druhém případě budeme určovat nové a vracející se zákazníky absolutně, to znamená bez ohledu na vnější filtry.

U relativních měřítek může být výsledek jiný v aktuálním období s ohledem na vnější filtry, které budou na měřítka působit. Uvažujme například filtr nastavený na konkrétní obchod a další filtr nastavený na konkrétní kategorii produktů. V takovémto případě bude například měřítko vracející počet nových zákazníků vracet počet nových zákazníků v aktuálním období, ale pouze ve vybraném obchodě a pouze zákazníky kteří nakoupili produkt z vybrané kategorie. Půjde tedy o zákazníky, kteří ve skutečnosti mohli nakupovat v daném obchodě již dříve, ale jiné produkty z jiné kategorie, než která je vybraná v průřezu. Nebo může jít o zákazníky kteří již nakupovali dříve v jiném obchodě. Tito zákazníci jsou proto noví pouze relativně, s ohledem na aktuální filtry působící na výpočty. Tyto relativní výpočty mohou být velmi užitečné, nicméně kladou velký důraz na použití ve správném kontextu. Každý filtr z jakéhokoliv vizuálu totiž může ovlivnit výsledek a také význam těchto relativních výpočtů.

Jiná situace pak bude u výpočtů absolutních. V těchto výpočtech budeme záměrně ignorovat všechny filtry kromě filtrů z tabulky 'Date' a filtrů z tabulky 'Customer'. Díky tomu dostaneme vždy počet všech nových nebo vracejících se zákazníků v aktuálním období, bez ohledu na filtry z jiné tabulky než z tabulky 'Customer'. Zachovávat filtry z tabulky 'Customer' má smysl i u absolutních výpočtů, protože uživatel reportů většinou očekává že výběrem například konkrétního města uvidí pouze zákazníky z tohoto města, výběrem určité věkové kategorie uvidí pouze ty zákazníky, kteří patří do dané kategorie a tak dále. Díky zachování filtrů z tabulky 'Customer' se pak můžeme dívat také na konkrétní zákazníky, a na období kdy se tito zákazníci stali zákazníky a kdy se vraceli.

Setkat se přirozeně můžeme také s kombinací dvou výše popsaných přístupů. Požadavkem může být například ignorovat filtry z tabulky s produkty ale zachovat filtry z tabulky s jednotlivými obchody. Těchto kombinací může být přirozeně velké množství, a proto se v tomto příspěvku budeme věnovat pouze výše popsaným absolutním a relativním výpočtům.

V následující části příspěvku si postupně ukážeme relativní a absolutní výpočty počtů nových a vracejících se zákazníků a měřítka která budou vracet prodeje těchto zákazníků. Pracovat budeme se cvičným Power BI souborem Adventure Works DW 2020.pbix. Soubor s řešením je dostupný ke stažení níže pod tímto příspěvkem.

V použitém souboru jsou navíc vytvořena dvě měřítka, měřítko [Prodeje] a měřítko [Počet zákazníků].

Měřítko:

Prodeje = SUM(Sales[Sales Amount])

Měřítko:

Počet zákazníků = DISTINCTCOUNT(Sales[CustomerKey])

Nová měřítka si může vložit do vizuálu matice spolu s roky, kvartály a měsíci z kalendářní tabulky v řádcích. Výchozí report, se kterým budeme pracovat tak může vypadat například následovně.

Na obrázku výše jsou navíc na levé straně dva průřezy. Jeden průřez s kategoriemi produktů z tabulky 'Product' a druhý průřez se zeměmi z tabulky 'Sales Territory'. Tyto dva průřezy budeme používat pro znázornění chování jednotlivých výpočtů v kontextu filtrů nastavených na použité sloupce.

Noví a vracející se zákazníci relativně

V relativních výpočtech, tedy ve výpočtech, ve kterých chceme zachovat všechny filtry, nemusíme příliš přemýšlet o manipulaci s filtry a jednotlivé výpočty jsou relativně jednoduché a intuitivní.  Začít můžeme počtem nových zákazníků. 

V tomto měřítku si nejdříve v první proměnné načteme všechny zákazníky, kteří jsou aktivní v aktuálním kontextu. K této tabulce s jedním sloupcem a všemi zákazníky kteří nakupovali v aktuálním období si navíc přidáme sloupec, ve kterém si uložíme u každého zákazníka první den, ve kterém každý jeden zákazník nakupoval poprvé. Při zjišťování prvního dne budeme odstraňovat filtry pouze z datumové tabulky, ale všechny ostatní filtry zůstanou aktivní. Pokud tabulku se zákazníky a prvním dnem ve kterém tito zákazníci nakupovali zafiltrujeme pouze na ty zákazníky, u kterých je datum prvního nákupu v aktuálním období (proměnná NoviZakaznici), budeme vědět že se jedná o nové zákazníky.

Měřítko:

Noví zákazníci =

VAR AktualniZakazniciAPrvniDen =

    ADDCOLUMNS

    (

        VALUES(Sales[CustomerKey]),

        "PrvniDenNakupu",

        CALCULATE

        (

            MIN(Sales[Order Date]),

            REMOVEFILTERS('Date')

        )

    )

VAR NoviZakaznici =

    FILTER

    (

        AktualniZakazniciAPrvniDen,

        [PrvniDenNakupu] IN VALUES('Date'[Date])

    )

VAR Vysledek = COUNTROWS(NoviZakaznici)

RETURN

    Vysledek

Pokud nové měřítko vložíme spolu s měřítkem [Počet zákazníků] do původního vizuálu matice, výsledek může vypadat například následovně.

Ke zjištění počtu vracejících se zákazníků můžeme použít podobný přístup, pouze v proměnné VracejiciSeZakaznici zafiltrujeme tabulku pouze na ty zákazníky, kteří svůj první nákup provedli před začátkem aktuálního období.

Měřítko:

Vracející se zákazníci =

VAR AktualniZakazniciAPrvniDen =

    ADDCOLUMNS

    (

        VALUES(Sales[CustomerKey]),

        "PrvniDenNakupu",

        CALCULATE

        (

            MIN(Sales[Order Date]),

            REMOVEFILTERS('Date')

        )

    )

VAR VracejiciSeZakaznici =

    FILTER

    (

        AktualniZakazniciAPrvniDen,

        [PrvniDenNakupu] < MIN('Date'[Date])

    )

VAR Vysledek =

    COUNTROWS(VracejiciSeZakaznici)

RETURN

    Vysledek

Nové měřítko si můžeme opět vložit do vizuálu matice a zobrazit si výsledky.

Měřítko [Počet zákazníků] vrací celkový počet zákazníků ve vybraném období. Měřítko [Počet nových zákazníků] pak vrací počet zákazníků, kteří v daném období nakupovali poprvé a měřítko [Vracející se zákazníci] vrací počet zákazníků, kteří v daném období nakupovali, ale svůj první nákup provedli kdykoliv před aktuálním obdobím. Součet hodnot měřítek [Počet nových zákazníků] a [Počet vracejících se zákazníků] pak odpovídá hodnotě měřítka [Počet zákazníků].

Všechna použitá měřítka budou respektovat jakékoliv vnější filtry. Pokud v průřezech na levé straně reportu vybereme například kategorii "Bikes" a zemi "Germany", měřítka budou vracet jiné hodnoty.

Nyní vrací všechna použitá měřítka hodnoty pouze pro kategorii produktů "Bikes" a zemi "Germany". Měřítko [Počet zákazníků] tedy vrací počet zákazníků kteří si v daném období zakoupili produkt z kategorie jízdních kol a současně tento nákup proběhl v Německu. Stejně tak měřítko [Počet nových zákazníků] vrací počet zákazníků, kteří si koupili v aktuálním období jízdní kolo v Německu, a pro tuto kombinaci filtrů se jedná o nové zákazníky. Neznamená to ale, že daný zákazník nenakoupil již dříve produkt z jiné kategorie nebo v jiném místě. U měřítka [Počet vracejících se zákazníků] jde o ty zákazníky, kteří si zakoupili v minulosti jízdní kolo v Německu, a v aktuálním období zakoupili znovu produkt z kategorie "Bikes" a zase v Německu.

Pokud bychom chtěli vidět například hodnoty prodejů pro jednotlivé skupiny zákazníků, můžeme použít tabulku se zafiltrovanými zákazníky ve filtru funkce CALCULATE() před vyhodnocením měřítka [Prodeje] následujícím způsobem.

Měřítko:

Prodeje noví zákazníci =

VAR AktualniZakazniciAPrvniDen =

    ADDCOLUMNS

    (

        VALUES(Sales[CustomerKey]),

        "PrvniDenNakupu",

        CALCULATE

        (

            MIN(Sales[Order Date]),

            REMOVEFILTERS('Date')

        )

    )

VAR NoviZakaznici =

    FILTER

    (

        AktualniZakazniciAPrvniDen,

        [PrvniDenNakupu] IN VALUES('Date'[Date])

    )

VAR Vysledek =

    CALCULATE

    (

        [Prodeje],

        KEEPFILTERS(NoviZakaznici)

    )

RETURN

    Vysledek

Měřítko:

Prodeje vracející se zákazníci =

VAR AktualniZakazniciAPrvniDen =

    ADDCOLUMNS

    (

        VALUES(Sales[CustomerKey]),

        "PrvniDenNakupu",

        CALCULATE

        (

            MIN(Sales[Order Date]),

            REMOVEFILTERS('Date')

        )

    )

VAR VracejiciSeZakaznici =

    FILTER

    (

        AktualniZakazniciAPrvniDen,

        [PrvniDenNakupu] < MIN('Date'[Date])

    )

VAR Vysledek =

    CALCULATE

    (

        [Prodeje],

        KEEPFILTERS(VracejiciSeZakaznici)

    )

RETURN

    Vysledek

Pokud obě nová měřítka vložíme do vizuálu matice, nebudeme se již dívat na počty zákazníků v jednotlivých kategoriích, ale na jejich prodeje.

Obdobně bychom mohli v prvním argumentu funkce CALCULATE() použít jakoukoliv jinou agregaci z faktové tabulky 'Sales'. V následující části příspěvku si ukážeme, jak vytvořit měřítka která budou ignorovat všechny vnější filtry kromě filtrů z tabulek 'Date' a 'Customer'.

Noví a vracející se zákazníci absolutně

U absolutních výpočtu budeme vycházet z logiky měřítek relativních, navíc ale budeme odstraňovat filtry ze všech tabulek kromě tabulky 'Customer' a 'Date'. U tabulky 'Date' budeme zachovávat filtry, protože chceme hodnoty měřítek zobrazit pro jednotlivá období, tedy v kontextu atributů (sloupců) z datumové tabulky. U tabulky 'Customer' budeme zachovávat filtry abychom mohli vyhodnocovat jednotlivá měřítka také v kontextu jednotlivých zákazníků nebo v kontextu jakýchkoliv kategorií zákazníků.

Měřítko:

Noví zákazníci (absolutně) =

VAR ZakazniciAPrvniDen =

    CALCULATETABLE

    (

        ADDCOLUMNS

        (

            VALUES(Sales[CustomerKey]),

            "PrvniDenNakupu",

            CALCULATE

            (

                MIN(Sales[Order Date])

            )

        ),

        ALLEXCEPT(Sales, Customer)

    )

VAR NoviZakaznici =

    FILTER

    (

        ZakazniciAPrvniDen,

        [PrvniDenNakupu] IN VALUES('Date'[Date])

    )

VAR Vysledek = COUNTROWS(NoviZakaznici)

RETURN

    Vysledek

V měřítku [Noví zákazníci (absolutně)] bude tentokrát tabulka v první proměnné ZakazniciAPrvniDen obsahovat všechny zákazníky a jejich první den nákupu, bez ohledu na jakékoliv filtry z jakékoliv jiné tabulky než z tabulky 'Customer'. Následně je tato tabulka se všemi zákazníky zafiltrována v proměnné NoviZakaznici pouze na ty zákazníky, kteří uskutečnili svůj první nákup v aktuálním období.

Měřítko [Vracející se zákazníci (absolutně)] pak může vypadat například následovně.

Měřítko:

Vracející se zákazníci (absolutně) =

VAR AktualniZakazniciAPrvniDen =

    CALCULATETABLE

    (

        ADDCOLUMNS

        (

            VALUES(Sales[CustomerKey]),

            "PrvniDenNakupu",

            CALCULATE

            (

                MIN(Sales[Order Date]),

                REMOVEFILTERS('Date')

            )

        ),

        ALLEXCEPT(Sales, Customer, 'Date')

    )

VAR VracejiciSeZakaznici =

    FILTER

    (

        AktualniZakazniciAPrvniDen,

        [PrvniDenNakupu] < MIN('Date'[Date])

    )

VAR Vysledek =

    COUNTROWS(VracejiciSeZakaznici)

RETURN

    Vysledek

V proměnné AktualniZakazniciAPrvniDen nyní na rozdíl od první proměnné v měřítku [Noví zákazníci (absolutně)] zachováváme filtry z tabulky 'Customer' a navíc také z tabulky 'Date', protože v této proměnné nyní potřebujeme mít pouze ty zákazníky, kteří nakupovali v aktuálním období. Důvodem je proměnná VracejiciSeZakaznici, ve které již nemůžeme na rozdíl od měřítka [Noví zákazníci (absolutně)] identifikovat aktuální zákazníky podle data prvního nákupu a funkce VALUES(), ale potřebujeme pouze ty zákazníky, kteří sice nakupovali v aktuálním období, ale první den nákupu je před aktuálním obdobím. Pokud obě nová měřítka vložíme do vizuálu matice, výsledek bude vypadat následovně.

Obdobně jako u relativních výpočtů i zde můžeme použít tabulku se zákazníky ve filtru funkce CALCULATE() a zobrazit si hodnoty měřítka [Prodeje] pro jednotlivé kategorie zákazníků.

Měřítko:

Prodeje noví zákazníci (absolutně) =

VAR ZakazniciAPrvniDen =

    CALCULATETABLE

    (

        ADDCOLUMNS

        (

            VALUES(Sales[CustomerKey]),

            "PrvniDenNakupu",

            CALCULATE

            (

                MIN(Sales[Order Date])

            )

        ),

        ALLEXCEPT(Sales, Customer)

    )

VAR NoviZakaznici =

    FILTER

    (

        ZakazniciAPrvniDen,

        [PrvniDenNakupu] IN VALUES('Date'[Date])

    )

VAR Vysledek =

    CALCULATE

    (

        [Prodeje],

        KEEPFILTERS(NoviZakaznici),

        ALLEXCEPT(Sales, Customer, 'Date')

    )

RETURN

    Vysledek

Text za výpočtem.

Měřítko:

Prodeje vracející se zákazníci (absolutně) =

VAR AktualniZakazniciAPrvniDen =

    CALCULATETABLE

    (

        ADDCOLUMNS

        (

            VALUES(Sales[CustomerKey]),

            "PrvniDenNakupu",

            CALCULATE

            (

                MIN(Sales[Order Date]),

                REMOVEFILTERS('Date')

            )

        ),

        ALLEXCEPT(Sales, Customer, 'Date')

    )

VAR VracejiciSeZakaznici =

    FILTER

    (

        AktualniZakazniciAPrvniDen,

        [PrvniDenNakupu] < MIN('Date'[Date])

    )

VAR Vysledek =

    CALCULATE

    (

        [Prodeje],

        KEEPFILTERS(VracejiciSeZakaznici),

        ALLEXCEPT(Sales, Customer, 'Date')

    )

RETURN

    Vysledek

Nová měřítka si opět můžeme vložit do vizuálu matice.

Všechna měřítka použitá ve vizuálu nyní nebudou reagovat na filtry nastavené na jakékoliv jiné sloupce než na sloupce z tabulky 'Date' nebo 'Customer'. Pokud tedy v průřezech vybereme například kategorii "Bikes" a stát "Germany", hodnoty měřítek se nezmění.

Další praktické příklady můžete najít na stránce DAX příklady nebo na stránce Power BI. Všechny videonávody jsou k dispozici na Youtubovém kanále pod tímto odkazem.

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