Skumulowana suma po produktach w DAXie

To jest problem, który napotkałem u jednego ze swoich klientów bardzo niedawno. Chciałem policzyć jaka sprzedaż jest generowana przez N pierwszych produktów w stylu pareto tj. jeśli pierwszy produkt ma sprzedaż 200, a drugi 100, to dla pierwszego będę miał 200, a dla drugiego 300. Taka kalkulacja pozwala pokazać, że przykładowo 90% sprzedaży jest generowane przez 7 produktów.

Początkowo, wypróbowałem podejście z wykorzystaniem RANKX. Niestety ze względu na wolumen danych (ponad 300 SKU wybieranych dynamicznie i miliony wierszy) wydajność była bardzo słaba. RANKX musiał być obliczony wielokrotnie dla każdej komórki z uwzględnieniem wszystkich wybranych produktów. Musiałem znaleźć inny podejście, i szczęśliwie ten post wskazał mi drogę. Miara, której ostatecznie użyłem:

Cumulative Total =
VAR total_sales = [Total Sales]
RETURN
    IF (
        NOT ( ISBLANK ( total_sales ) ),
        CALCULATE (
            [Total Sales],
            FILTER ( ALLSELECTED ( Sales[Product] )total_sales <= [Total Sales] )
        )
    )

A to jak działa w akcji. Mam prostą tabelę z produktami i sprzedażą:

Tworzę miarę [Total Sales] = SUM(Sales[Sales]) i moją miarę [Cumulative Total]. Po wrzuceniu ich do tabeli, otrzymuję:

I dokładnie tak jak chcę, mam skumulowaną sprzedaż licząc od najlepszego produktu. Przy wybraniu produktów B,F,C, otrzymuję:

Więc, jak to działa?

VAR total_sales = [Total Sales]

Ta część obliczy i “zapisze” sprzedaż dla produktu z kontekstu filtra. Warto zwrócić uwagę na to, że zmianna jest obliczana raz przy deklaracji. W przykładzie dla produktów B,F,C total_sales to kolejno 400,315,243.

IF (
NOT ( ISBLANK ( total_sales ))

Ta część odsiewa produkty, które nie mają żadnej sprzedaży.

CALCULATE ([Total Sales],

Ta część policzy całkowitą sprzedaż dla produktów wybranych w filtrze w następnym kroku.

FILTER ( ALLSELECTED ( Sales[Product] ), total_sales <= [Total Sales] )

To jest najważniejsza, ale też najdziwniejsza część. Funkcja FILTER zwraca tabelę, która reprezentuje podzbiór innej tabeli lub wyrażenia. W przypadku przykładu pierwotną tabelą jest ALLSELECTED ( Sales[Product] ).a Następnie, funkcja przejdize przez każdy element tabeli (w przykładzie będize to B,F,C) i sprawdzi czy total_sales (czyli zapisana wartość dla konkretnej komórki) jest mniejsza lub równa [Total Sales] dla elementu tabeli. Następnie funkcja wybiera tyle te elementy, które spełniają warunek i liczy dla nich [Total Sales].

To może wydawać się zagmatwane, więc pokażę to dokładnie na drugim przykładzie:

Miara w DAX jest liczona oddzielnie dla każdej komórki, ponieważ znajdują się one w innych kontakstach filtra.  Dlatego pokażę oddzielne wyliczenia dla komórek C,F,B i jak osiągają wartości 958, 715 i 400.

Obliczenia dla produktu C:

total_sales (jak już wyjaśniłem wyżej) będzie równało się 243

NOT(ISBLANK(total_sales) to PRAWDA, ponieważ 243 nie jest puste

CALCULATE([Total Sales] zależy od tego co jest w filtrze

FILTER(ALLSELECTED(Sales[Product]) ma małą tabelę z kolumną [Product] i wierszami C,F,B. Sprawdza dla każdego wiersza czy [Total Sales] jest większe lub równe niż 243. Dla C,[Total Sales] = 243, więc warunek jest spełniony. Dla F, [Total Sales] = 315 też jest większy niż 243. Dla B, [Total Sales] to 400, więc jest większy niż 243. Filtr przekaże wszystkie wiersze, a CALCULATE policzy [Total Sales] dla nich. Dlatego Cumulative Total to 243+315+400=958.

 

Obliczenia dla produktu F:

total_sales będzie równało się 315

NOT(ISBLANK(total_sales) to PRAWDA, ponieważ 315 nie jest puste

CALCULATE([Total Sales] zależy od tego co jest w filtrze

FILTER(ALLSELECTED(Sales[Product]) ma małą tabelę z kolumną [Product] i wierszami C,F,B. Sprawdza dla każdego wiersza czy [Total Sales] jest większe lub równe niż 315. Dla C,[Total Sales] = 243, więc mniejszy niż 315. Warunek nie jest spełniony. Dla F, [Total Sales] = 315, więc jest równy 315. Dla B, [Total Sales] to 400, więc jest większy niż 315. Filtr przekaże wiersze F i B, a CALCULATE policzy [Total Sales] dla nich. Dlatego Cumulative Total to 315+400=715.

 

Obliczenia dla produktu B:

total_sales będzie równało się 400

NOT(ISBLANK(total_sales) to PRAWDA, ponieważ 400 nie jest puste

CALCULATE([Total Sales] zależy od tego co jest w filtrze

FILTER(ALLSELECTED(Sales[Product]) ma małą tabelę z kolumną [Product] i wierszami C,F,B. Sprawdza dla każdego wiersza czy [Total Sales] jest większe lub równe niż 400. Dla C,[Total Sales] = 243, więc mniejszy niż 400. Warunek nie jest spełniony. Dla F, [Total Sales] = 315, więc też mniejszy niż total_sales (400). Warunek nie jest spełniony. Dla B, [Total Sales] to 400, więc jest równy total_sales. Filtr przekaże wiersz B, a CALCULATE policzy [Total Sales] dla niego. Dlatego Cumulative Total to 400.

Wydaje mi się, że jest to najwydajniejszy sposób liczenia miary tego typu, szczególnie, że nie polega na RANKX.

Thanks,

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *