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,