Power BI DAX – Agregowanie zagregowanych danych

DAX jest naprawdę potężnym językiem. Mimo początkowo złudnego przekonania, że jest on trywialny po pewnym czasie ukazuje swoje prawdziwe oblicze na podstawie którego możemy dojść do wniosku, że bardziej wyrafinowane kalkulacje wymagają czasu i wielu testów. Budując liczne kalkulacje spędziłem sporo czasu próbując wypracować pewne wzorce, które mógłbym używać w określonych sytuacjach. Jednym z takich wzorców jest technika, która pozwala budować dynamiczne miary obliczające agregat na już pogrupowanych danych.
O co właściwie mi chodzi? O kalkulacje takie jak suma średnich, suma iloczynów czy też suma wartości sprzedaży na na ostatnich fakturach wystawionych dla naszych klientów. Jak stworzyć tego typu miary aby nie ograniczać interaktywności Power BI? Postaram się to Wam przedstawić w ramach niniejszego artykułu do którego lektury serdecznie zapraszam.

Do przykładów posłuży nam AdventureWorksDW. Na sam początek coś prostego czyli suma iloczynów. Scenariusz ten jest na tyle popularny, że prędzej czy później każdy na niego napotka. Mając dwie kolumny jeden z ilością sprzedanych przedmiotów, a drugą z ich ceną musimy najpierw je przemnożyć, a dopiero później posumować tak aby dostać prawidłową wartość transakcji w postaci sumy.

Scenariusz ten został zobrazowany poniżej gdzie najpierw tworzymy iloczyn ilości oraz ceny by na samym końcu powstały wyniki częściowe zsumować:

Patrząc na ten obrazek możemy dojść do wniosku, że może zrobimy dodatkową kolumnę kalkulowaną zawierającą iloczyn, a następnie ją posumujemy. Jest to dobre rozwiązanie i z całą pewnością będzie działało jednakże iloczyn w postaci kolumny zostanie zmaterializowany co nie zawsze jest czymś pożądanym w szczególności gdy podobnych miar jest dużo. Jeśli nie chcemy tworzyć dodatkowych kolumn to możemy użyć funkcji SUMX, która jest iteratorem i najpierw wykona wyrażenie podane jako parametr wiersz po wierszu, a następnie powstałe wyniki posumuje. Jej składnia jest bardzo prosta i w przypadku przedstawionym na powyższym zrzucie ekranowym wygląda następująco:

=SUMX('Tabela', 'Tabela'[Ilosc]*'Tabela'[Cena])

Wypróbujmy ją w praktyce. Przypuśćmy, że mamy następującą tabelę testową zawierającą numer zamówienia, jego linię, cenę oraz ilość:

Chcemy stworzyć miarę, która przemnoży zgodnie ze schematem UnitPrice oraz OrderQuantity:

SalesAmount = SUMX('FactInternetSales';[UnitPrice]*[OrderQuantity])

Po dorzuceniu jej do tabeli widzimy, że miara oblicza się dobrze w każdym kontekście tj. zarówno na poziomie pojedynczej linii jak i podsumowania:

To było proste prawda? W podobny sposób działają wszystkie iteratory czyli funkcje z suffiksem X w nazwie SUMX, COUNTX itp. Przetwarzanie wiersz po wierszu ma również swoją ciemną stronę i może być problematyczne pod kątem wydajnościowym. Warto o tym pamiętać i ładować do Power BI tylko niezbędne dane na najwyższym poziomie agregacji, który jednocześnie pozwoli sprostać postawionym wymogom biznesowym. Rozwiązaniem tego problemu mogą być również agregacje (pisałem o nich tutaj). Pamiętajmy również o tym, że potencjalne problemy występują tylko w niektórych przypadkach i dla większości modeli nie stanowią one problemu.

Spróbujmy czegoś nieco bardziej skomplikowanego. W kolejnym przykładzie chcemy agregować wynik jakiegoś działania tylko, że nie poruszamy się już na poziomie pojedynczego wiersza, a nieco wyżej. Przypuśćmy, że chcemy stworzyć jedną z miar na dashboardzie, która pokaże ile średnio zamawiają miesięcznie klienci w danym regionie. Miara w swoim założeniu powinna pokazywać średnią miesięczną na poziomie wyższym niż miesiąc (np. kwartał albo rok), dla lepszego zaprojektujemy ją w taki sposób aby na poziomie miesiąca pokazywała sumę sprzedaży, a na wyższym poziomie pokazywała średnią miesięczną. Pierwszą rzeczą na którą muszę zwrócić uwagę to fakt, czy mam wszystkie pola na których będzie opierała się moja kalkulacja. Wymiar czasu z bazy AdventureWorksDW nie posiada pola będącego połączeniem roku i miesiąca dlatego też taką kolumnę dodałem opierając się na poniższej kalkulacji:

YearMonth = [CalendarYear]&FORMAT([MonthNumberOfYear];"00")

Następnie muszę napisać taką miarę, która policzy sumę miesięcznych transakcji, po to by na wyższym poziomie agregacji (np. rocznym) pokazać średnią sprzedaż miesięczną. Pierwszym wyzwaniem jest to aby wskazać w jakiś sposób, że chcemy posumować wartości per miesiąc, a następnie na podstawie tej “podsumy” wyliczyć średnią. Scenariusz nieco podobny do poprzedniego tylko na innym poziomie – jedyne czego możemy być pewni to fakt, że musimy użyć iteratora AVERAGEX. Jego pierwszym argumentem będzie funkcja VALUES z kolumną YearMonth przekazaną jako parametr. Funkcja ta wybiera unikalną listę wartości z kolumny podanej jako parametr, następnie jako drugi parametr iteratora podajemy sumę na polu Sales Amount opatrzoną CALCULATE (funkcja ta jest potrzebna aby podczas obliczania sumy była ona w kontekście filtra). Ostatecznie miara przedstawia się następująco:

MonthlyAverage = AVERAGEX(VALUES('DimDate'[YearMonth]);CALCULATE(SUM(FactInternetSales[SalesAmount])))

Po przerzuceniu miary do tabeli widzimy, że działa ona poprawnie (na poziomie miesiąca mamy sumę, a na poziomie roku średnią miesięczną):

Na podsumowaniu mamy średnią miesięczną dla całego wybranego okresu, która zostanie wyświetlona na np. obiekcie typu Card, który z kolei świetnie nadaje się do wizualizacji różnego rodzaju zestawień podsumowujących:

Możemy oczywiście sterować na jakim poziomie chcemy agregować dane, również używając wielu kolumn, a nie tylko jednej (do tego celu możemy użyć zamiast VALUES np. SUMMARIZE czy GROUPBY).

Kolejny scenariusz będzie również bardzo ciekawy. Przypuśćmy, że chcemy stworzyć miarę, która obliczy średnią sprzedaż miesięczną z ostatnich 6 miesięcy + bieżący “otwarty” miesiąc. Do tego typu kalkulacji musimy podejść stopniowo – w pierwszym kroku musimy stworzyć zakres sześciu miesięcy, który pozwoli nam uzyskać określony kontekst czasowy. W tym celu posłużymy się oczywiście zmiennymi – pożądany kod wygląda następująco:

EVALUATE
VAR vLastDate =
    CALCULATETABLE ( ENDOFMONTH ( 'DimDate'[FullDateAlternateKey] ) )
RETURN
    DATESBETWEEN (
        'DimDate'[FullDateAlternateKey],
        STARTOFMONTH ( DATEADD ( vLastDate, -6, MONTH ) ),
        ENDOFMONTH ( vLastDate )
    )

Rezultat:

Ze względu na to, że wyświetlamy samą datę bez dodatkowego kontekstu to zmienna vLastDate zwróciła ostatnią dostępną datę w całym modelu czyli 31 grudnia 2014. Funkcja DATESBETWEEN zwróciła zestaw wierszy od 1 czerwca 2014 (vLastDate – 6 miesięcy) do 31 grudnia. Zwróćcie uwagę proszę na użycie STARTOFMONTH oraz ENDOFMONTH – oznacza to, że poruszamy się w pełnych miesiącach czyli np. dla grudnia bez względu na dzień miesiąca datą oddaloną o 6 miesięcy będzie 1 czerwca itp itd. Oczywiście w zależności od kontekstu data ta odpowiednio się dostosuje. Tak więc to co zwróciła nam funkcja DATESBETWEEN będziemy mogli przekazać jako filtr do naszej kalkulacji.

W trzecim kroku spróbujemy policzyć średnią miesięczną sprzedaż z ostatnich sześciu miesięcy. Również ten krok dla przejrzystości umieścimy w zmiennej:

vValue=CALCULATE (
        AVERAGEX (
            CALCULATETABLE ( VALUES ( 'DimDate'[YearMonth] ); vDates );
            CALCULATE ( SUM ( FactInternetSales[SalesAmount] ) )
        );
        ALL ( DimDate )
    )

Używamy funkcji AVERAGEX, która będzie iterować po ostatnich 6 miesiącach. Zbiorem po którym się poruszamy nie jest pojedynczy wiersz lecz suma pola Sales Amount obliczona właśnie dla ostatnich 6 miesięcy, dokonaliśmy tego używając CALCULATETABLE który wylicza listę miesięcy na podstawie filtra będącego obliczoną wcześniej zmienną. Koncepcja przedstawiona została poniżej:

Całość wieńczy CALCULATE z nadpisanym kontekstem na dacie. Cała miara przedstawia się następująco:

Rolling average of monthly sales = 
	VAR vLastDate =
    CALCULATETABLE ( ENDOFMONTH ( 'DimDate'[FullDateAlternateKey] ) )
VAR vDates =
    DATESBETWEEN (
        'DimDate'[FullDateAlternateKey];
        STARTOFMONTH ( DATEADD ( vLastDate; -6; MONTH ) );
        ENDOFMONTH ( vLastDate )
    )
VAR vValue =
    CALCULATE (
        AVERAGEX (
            CALCULATETABLE ( VALUES ( 'DimDate'[YearMonth] ); vDates );
            CALCULATE ( SUM ( FactInternetSales[SalesAmount] ) )
        );
        ALL ( DimDate )
    )
RETURN vValue

Miara mimo pewnego stopnia skomplikowania nie jest bardzo trudna po rozebraniu jej na części pierwsze. Poniżej możecie zauważyć jak się ona sprawuje w praktyce dla zbioru klientów:

Oczywiście miara może być w dalszym ciągu dostosowana tak aby obliczała się np. tylko dla miesięcy w których była sprzedaż, lub np. żeby na poziomie roku pokazywała coś innego (w powyższym przykładzie pokazuje średnią sprzedaż miesięczną od czerwca do grudnia) – z tym nie powinniśmy mieć większych problemów.

DAX jest naprawdę potężnym językiem. Pozwala nam osiągnąć naprawdę świetne wyniki stosunkowo małym nakładem sił. Oczywiście jestem świadomy, że taki sam efekt możemy osiągnąć używając innego podejścia i niezmiernie mnie to cieszy bo świadczy to tylko i wyłącznie o sile języka. Mam nadzieję, że powyższe przykłady chociaż w najmniejszym stopniu pozwoliły Wam zrozumieć zasadę działania DAX w przypadku problemu agregatów obliczanych na innych agregatach.