Kurs Power BI: DAX: iteratory SUMX, AVERAGEX i miary z VAR

W skrócieIteratory DAX, takie jak SUMX i AVERAGEX, przechodzą tabelę wiersz po wierszu, liczą wyrażenie w każdym wierszu i dopiero na końcu składają wynik. Na danych Nordvelli liczymy marżę katalogową z RELATED, porządkujemy miary zmiennymi VAR i RETURN, dzielimy bezpiecznie przez DIVIDE i debugujemy miarę.

W poprzednich rozdziałach liczyliśmy sumy i średnie na gotowych kolumnach. Teraz robi się ciekawie. W Nordvelli dyrektor sprzedaży pyta: „ile realnie zarabiamy na każdej sprzedanej sztuce, a nie na całości obrotu?". Zwykły SUM tego nie policzy, bo marża jednostkowa leży w cenniku, ilość w tabeli sprzedaży, a iloczyn trzeba policzyć w każdym wierszu z osobna. Tu wchodzą iteratory, czyli funkcje, które przechodzą tabelę wiersz po wierszu, liczą wyrażenie w każdym z nich i dopiero na końcu składają wynik. Do tego pokażemy zmienne VAR, bezpieczne dzielenie DIVIDE i prosty sposób na debugowanie miary, która „coś liczy, ale nie to co trzeba".

SUMX i AVERAGEX: iteracja wiersz po wierszu

Co chcemy osiągnąć. Wartość sprzedaży Nordvelli trzeba czasem policzyć z dwóch kolumn naraz, mnożąc ilość przez cenę w każdej transakcji, bo tego iloczynu nie ma w tabeli. Chcemy poznać iteratory, które liczą wyrażenie wiersz po wierszu.

Nazwy iteratorów w DAX kończą się na literę X: SUMX, AVERAGEX, MINX, MAXX, COUNTX, RANKX. Każdy z nich przyjmuje co najmniej dwa argumenty: tabelę do przejścia oraz wyrażenie, które ma zostać policzone osobno w każdym wierszu tej tabeli. To wyrażenie działa w tak zwanym kontekście wiersza (row context), czyli w danym momencie „widzi" tylko wartości z jednego, aktualnie przetwarzanego wiersza. Dopiero po przejściu całej tabeli funkcja agreguje wszystkie cząstkowe wyniki, SUMX je dodaje, AVERAGEX uśrednia.

Krok 1. Wszystko z tej lekcji zbudujemy na dwóch nowych stronach raportu. Na pasku stron kliknij zielony plus, kliknij dwukrotnie zakładkę nowej strony, wpisz Iteratory i naciśnij Enter.

Zacznijmy od najprostszego przykładu, który obnaża różnicę. W Nordvelli chcemy policzyć całkowitą wartość netto sprzedaży, mnożąc ilość przez cenę jednostkową w każdej transakcji. Zwróć uwagę, że tabela FaktSprzedaz ma kolumny Ilosc i CenaNetto, ale iloczyn tych dwóch nie jest przechowywany jako oddzielna kolumna. Musimy go policzyć wiersz po wierszu. Ten sam iloczyn liczyliśmy już w lekcji 07 (miara Iloczyn kolumn przez SUMX). Tu rozkładamy na części, co SUMX robi w środku.

Krok 2. Kliknij w panelu Dane prawym przyciskiem na tabelę FaktSprzedaz, wybierz Nowa miara, wklej formułę i zatwierdź ją klawiszem Enter. Na karcie Narzędzia miar w polu Format wpisz #,0 "zł". Ten sam format ustaw każdej mierze kwotowej z tej lekcji:

Wartość z iloczynu =
SUMX (
    FaktSprzedaz,
    FaktSprzedaz[Ilosc] * FaktSprzedaz[CenaNetto]
)

Krok 3. Dla porównania utwórz miarę z błędem, o którym zaraz piszemy: iloczyn dwóch sum zamiast sumy iloczynów.

Wartość z sum osobno =
SUM ( FaktSprzedaz[Ilosc] ) * SUM ( FaktSprzedaz[CenaNetto] )

Krok 4. Kliknij puste miejsce strony, w panelu Wizualizacje wybierz Karta i zaznacz w panelu Dane trzy miary: Łączna sprzedaż, Wartość z iloczynu i Wartość z sum osobno. Kartę rozciągnij za prawy dolny róg na szerokość strony, żeby liczby się nie ucinały.

Iterator i suma kolumny WartoscNetto dają to samo, dokładnie 467,78 mln zł dla całego zbioru. To nie przypadek: w Nordvelli kolumna WartoscNetto powstała właśnie jako iloczyn ilości i ceny, więc SUMX odtwarza ją transakcja po transakcji. Wartość z sum osobno pokazuje, dlaczego mechanizm ma znaczenie: SUM(FaktSprzedaz[Ilosc]) * SUM(FaktSprzedaz[CenaNetto]) mnoży sumę wszystkich sztuk przez sumę wszystkich cen, co miesza ze sobą tanie akcesoria i drogie komputery.

Teraz AVERAGEX. Załóżmy, że chcemy poznać średnią wartość jednej transakcji w rozbiciu na sklepy. Nie chodzi o średnią z kolumny, tylko o średnią z wartości policzonej w każdym wierszu:

Średnia transakcja przez AVERAGEX =
AVERAGEX (
    FaktSprzedaz,
    FaktSprzedaz[WartoscNetto]
)

Krok 5. Tej mierze ustaw format #,0.00, taki sam jak ma miara Średnia wartość transakcji z lekcji 07. Kliknij puste miejsce strony pod kartą i wybierz Macierz. Do obszaru Wiersze przeciągnij DimSklepy[Miasto], do Wartości Średnią wartość transakcji i Średnią transakcję przez AVERAGEX. W Formatuj element wizualny, Siatka, Opcje, ustaw Globalny rozmiar czcionki na 14 i wydłuż macierz, żeby zmieściło się jedenaście miast.

Każda pozycja to średnia policzona po wszystkich transakcjach danego miasta. Różnice między miastami są niewielkie, od 4 156 zł w Bydgoszczy do 4 416 zł w Lublinie, więc koszyk zakupowy wygląda w całej sieci podobnie.

Kiedy iterator zamiast SUM (marża z cennika)

Co chcemy osiągnąć. Dyrektor Nordvelli pyta o marżę liczoną z cennika: marża jednej sztuki jest w tabeli produktów, a liczba sprzedanych sztuk w transakcjach. Chcemy połączyć jedno z drugim miarą, która da poprawny wynik w każdym przekroju raportu.

Najczęstszy realny scenariusz, w którym iterator jest jedynym poprawnym wyjściem, wygląda tak: potrzebna liczba leży w wymiarze, a mnożnik w faktach. Wróćmy do pytania dyrektora Nordvelli o marżę. W cenniku, czyli w tabeli DimProdukty, każdy produkt ma kolumnę MarzaNetto z marżą na jednej sztuce: laptop Kelvani Vela 13 kosztuje 2 849 zł i daje 242 zł marży. Żeby policzyć, ile firma zarobiła na całej sprzedaży, trzeba wziąć każdą transakcję, sprawdzić, ile sztuk poszło, sięgnąć po marżę jednostkową tego produktu i dopiero pomnożyć.

Zobacz też: Szkolenie Power BI - Desktop, DAX i Online + Copilot

Uwaga na pułapkę nazw: tabela faktów ma własną kolumnę MarzaNetto, ale ona trzyma marżę całej pozycji (WartoscNetto minus KosztWlasny), a nie marżę jednej sztuki. Jednostkowa marża z cennika siedzi wyłącznie w DimProdukty. Dlatego sięgamy po nią funkcją RELATED, która w kontekście wiersza przechodzi po relacji do tabeli wymiaru:

Marża katalogowa =
SUMX (
    FaktSprzedaz,
    FaktSprzedaz[Ilosc] * RELATED ( DimProdukty[MarzaNetto] )
)

Prześledźmy, co dzieje się krok po kroku. SUMX bierze pierwszy wiersz FaktSprzedaz. Odczytuje z niego ilość, przechodzi relacją do produktu i pobiera jego marżę jednostkową. Mnoży jedno przez drugie, zapamiętuje wynik i przechodzi do następnego wiersza. Po przejściu wszystkich 108 675 transakcji sumuje cząstkowe marże i zwraca 72 287 835 zł. Na wizualu widzisz jedną liczbę, ale za nią kryje się ponad sto tysięcy niezależnych mnożeń. Tego nie da się zapisać przez SUM, bo w żadnej kolumnie nie leży gotowy iloczyn: ilość jest w faktach, marża jednostkowa w wymiarze.

Krok 6. Wstaw obok macierzy miast drugą macierz: do Wierszy przeciągnij DimProdukty[Kategoria], do Wartości Łączną sprzedaż i Marżę katalogową. Położenie i rozmiar najwygodniej ustawisz w Formatuj element wizualny, karta Ogólne, Właściwości (u nas pozycja 880 na 329, rozmiar 620 na 260). Dopiero to zestawienie pokazuje, gdzie leży zarobek, a gdzie tylko obrót:

Zmienne VAR i RETURN dla czytelności

Co chcemy osiągnąć. Miary dla Nordvelli rosną, a te same fragmenty kodu powtarzają się w nich kilka razy. Chcemy pisać je tak, żeby inna osoba w zespole mogła je przeczytać i poprawić, a powtarzane fragmenty liczyły się raz.

Zmienne VAR pojawiły się już w lekcji 10 (miary po rabacie) i 11 (Wskaźnik NPS). Tu pokazujemy, co dają, na jednej mierze w dwóch wersjach. Kiedy miary rosną, kod robi się nieczytelny, a te same fragmenty powtarzają się kilka razy. Rozwiązaniem są zmienne. Konstrukcja VAR nazwa = wyrażenie pozwala policzyć fragment raz, nadać mu czytelną nazwę i użyć jej dalej po słowie RETURN. Zmienna w DAX jest liczona dokładnie w tym miejscu, w którym została zadeklarowana, i przechowuje swoją wartość niezależnie od tego, jak później zmienia się kontekst. To bardzo ważna właściwość, do której wrócimy przy debugowaniu.

Porównajmy dwie wersje tej samej miary: marża katalogowa w procentach sprzedaży. Najpierw wersja bez zmiennych, ściśnięta w jedną linię, którą trudno odczytać i jeszcze trudniej poprawić:

Marża katalogowa bez VAR =
SUMX ( FaktSprzedaz, FaktSprzedaz[Ilosc] * RELATED ( DimProdukty[MarzaNetto] ) ) / SUM ( FaktSprzedaz[WartoscNetto] )

A teraz ta sama logika ze zmiennymi. Każdy fragment ma nazwę, którą rozumie także osoba, która nie pisała tej miary. Dodatkowo iterator SUMX jest liczony tylko raz: w rozbudowanej wersji miary możesz użyć zmiennej MarzaKatalogowa kilka razy bez powtarzania kosztownego obliczenia:

Marża katalogowa % =
VAR MarzaKatalogowa =
    SUMX (
        FaktSprzedaz,
        FaktSprzedaz[Ilosc] * RELATED ( DimProdukty[MarzaNetto] )
    )
VAR SprzedazNetto = SUM ( FaktSprzedaz[WartoscNetto] )
RETURN
    DIVIDE ( MarzaKatalogowa, SprzedazNetto )

Krok 7. Obu miarom ustaw format 0.0% i zaznacz je, żeby trafiły do macierzy kategorii. Macierz poszerz (Formatuj element wizualny, Ogólne, Właściwości, Szerokość 1000), żeby zmieściły się nagłówki.

Krok 8. Pod macierzą kategorii wstaw trzecią macierz: do Wierszy przeciągnij DimSklepy[Region], do Wartości Marżę katalogową %. Zobaczysz udział marży w sprzedaży dla każdego regionu. W Nordvelli wszystkie regiony trzymają się blisko 15,5 procent, co samo w sobie jest informacją: struktura asortymentu jest w całej sieci podobna, a różnice w wyniku biorą się z wolumenu, nie z rentowności.

DIVIDE i bezpieczne dzielenie

Co chcemy osiągnąć. Nordvella śledzi, ile złotych sprzedaży przypada na jeden zwrot, a w sklepie i miesiącu bez zwrotów mianownik wynosi zero. Chcemy, żeby taki wskaźnik nie psuł raportu błędem ani nieskończonością.

Zwróć uwagę, że w mierze marży procentowej użyliśmy DIVIDE, a nie zwykłego operatora ukośnika. To nie kosmetyka. Weźmy właśnie wskaźnik sprzedaży na jeden zwrot. Mianownikiem jest liczba zwróconych pozycji, a ta w konkretnym sklepie i konkretnym miesiącu potrafi wynosić zero. Nordvella Warszawa Centrum w październiku 2024 sprzedała za 705 942 zł i nie przyjęła ani jednego zwrotu.

Funkcja DIVIDE przyjmuje trzy argumenty: licznik, mianownik oraz opcjonalną wartość zwracaną, gdy mianownik jest zerem lub pusty. Jeśli trzeciego argumentu nie podasz, DIVIDE zwróci pusto (BLANK), czyli po prostu pustą komórkę. Operator ukośnika w tej samej sytuacji zwraca Nieskończoność i ta wartość idzie dalej do sortowania, sumy i wykresu. Porównaj obie wersje na jednym ekranie.

Krok 1. Dodaj drugą stronę i nazwij ją DIVIDE. Utwórz na niej trzy miary, każdą osobno (Nowa miara, wklej, Enter). Pierwsza liczy zwrócone pozycje, format #,0:

Liczba zwróconych pozycji =
CALCULATE (
    COUNTROWS ( FaktSprzedaz ),
    FaktSprzedaz[CzyZwrot] = 1
)

Druga dzieli sprzedaż przez liczbę zwrotów operatorem ukośnika. To wersja ryzykowna: gdy w danym miesiącu nie było zwrotu, mianownik jest pusty. Format #,0.00:

Sprzedaż na zwrot ukośnikiem =
    [Łączna sprzedaż] / [Liczba zwróconych pozycji]

Trzecia robi to samo przez DIVIDE, bez trzeciego argumentu, więc przy pustym mianowniku zostawi pustą komórkę. Format #,0.00:

Sprzedaż na zwrot przez DIVIDE =
    DIVIDE ( [Łączna sprzedaż], [Liczba zwróconych pozycji] )

Krok 2. Kliknij puste miejsce strony i wybierz Tabela. Zaznacz w panelu Dane DimSklepy[Nazwa], Łączną sprzedaż, Sprzedaż na zwrot ukośnikiem i Sprzedaż na zwrot przez DIVIDE. W Formatuj element wizualny ustaw czcionkę 14, a na karcie Ogólne rozmiar 1100 na 690, żeby zmieściło się piętnaście sklepów.

Krok 3. Zawęź tabelę do października 2024. Na karcie Widok włącz okienko Filtry, przeciągnij DimKalendarz[RokMiesiac] do sekcji Filtry dla tej wizualizacji, w polu wyszukiwania karty filtra wpisz 2024-10 i zaznacz tę pozycję. Filtr wizualizacji działa tylko na tabelę, więc następna macierz na tej stronie pokaże dane z całego okresu.

Kiedy chcesz świadomie pokazać zero zamiast pustej komórki, na przykład we wskaźniku, który ma zawsze mieć wartość liczbową, podaj trzeci argument. Policzmy współczynnik zwrotów Nordvelli, czyli udział zwróconych pozycji we wszystkich transakcjach:

Współczynnik zwrotów przez DIVIDE =
VAR LiczbaZwrotow =
    CALCULATE (
        COUNTROWS ( FaktSprzedaz ),
        FaktSprzedaz[CzyZwrot] = 1
    )
VAR LiczbaTransakcji = COUNTROWS ( FaktSprzedaz )
RETURN
    DIVIDE ( LiczbaZwrotow, LiczbaTransakcji, 0 )

Krok 4. Ustaw tej mierze format 0.0%. Pod tabelą wstaw macierz: do Wierszy przeciągnij DimProdukty[Kategoria], do Wartości Współczynnik zwrotów przez DIVIDE i Liczbę transakcji. Trzeci argument sprawia, że w komórce bez ani jednej transakcji pojawi się 0 procent, a nie pustka. W całej Nordvelli współczynnik trzyma się między 2,4 a 2,6 procent, więc żadna kategoria nie odstaje na tyle, żeby podejrzewać problem z jakością albo z opisem produktu.

Debugowanie miary przez wynik pośredni

Co chcemy osiągnąć. Miara marży katalogowej Nordvelli daje na wykresie podejrzany wynik, a nie wiadomo, czy błąd jest w liczniku, czy w mianowniku. Chcemy sprawdzać takie miary krok po kroku przez wynik pośredni, zamiast zgadywać.

Prędzej czy później napiszesz miarę, która liczy „coś", ale nie to, co chcesz. Zamiast wpatrywać się w kod, wykorzystaj to, że zmienne VAR trzymają wynik pośredni. Trik jest prosty: tymczasowo zwróć samą zmienną zamiast finalnego wyrażenia i sprawdź, czy jej wartość na wizualu ma sens. To prosta i skuteczna metoda debugowania w DAX, bo pozwala rozłożyć skomplikowaną miarę na kawałki i po kolei sprawdzić każdy.

Krok 1. Wróć na stronę Iteratory i wstaw w wolnym miejscu kartę z miarą Marża katalogowa %. Pokaże 15,5%.

Wróćmy do miary marży katalogowej. Powiedzmy, że wynik na wizualu jest podejrzany. Nie wiemy, czy problem tkwi w liczniku (SUMX z RELATED) czy w mianowniku (suma sprzedaży). Krok 2. Zaznacz miarę w panelu Dane i w pasku formuł podmień RETURN tak, żeby zwracał samą zmienną z licznika:

Marża katalogowa % =
VAR MarzaKatalogowa =
    SUMX (
        FaktSprzedaz,
        FaktSprzedaz[Ilosc] * RELATED ( DimProdukty[MarzaNetto] )
    )
VAR SprzedazNetto = SUM ( FaktSprzedaz[WartoscNetto] )
RETURN
    MarzaKatalogowa   -- tymczasowo zwracamy tylko licznik

Format miary zostaje procentowy, więc karta pokaże dziwaczne 7228,8 mln%. To nie jest błąd, tylko skutek tego, że sprawdzamy licznik miarą sformatowaną jak wskaźnik. Liczba przed jednostką jest tym, co Cię interesuje: 72,3 mln zł marży katalogowej to wartość sensowna wobec 468 mln zł sprzedaży, czyli licznik jest zdrowy. W kolejnym kroku zwracasz SprzedazNetto i karta pokaże 46777,5 mln%, czyli 467,8 mln zł, więc mianownik też jest zdrowy. Metoda „zwróć jedną zmienną, sprawdź, przejdź do następnej" pozwala precyzyjnie wskazać, który fragment miary kłamie, zamiast zgadywać. Po znalezieniu błędu przywracasz właściwy RETURN z DIVIDE. W pliku efektu miara ma już przywrócone DIVIDE i karta pokazuje 15,5%.

Warto poznać drugi mechanizm debugowania, który przyda się przy iteratorach. Czasem chcesz zobaczyć nie jedną liczbę, ale całą tabelę cząstkowych wyników wiersz po wierszu. Do tego służy funkcja ADDCOLUMNS połączona z tabelą, którą podglądasz w widoku danych albo w narzędziu DAX Studio. Poniżej dodajemy do pięciu największych transakcji kolumnę z marżą katalogową pozycji, żeby wzrokowo potwierdzić, że mnożenie działa na pojedynczym wierszu:

Podgląd marży pozycji =
ADDCOLUMNS (
    TOPN ( 5, FaktSprzedaz, FaktSprzedaz[WartoscNetto], DESC ),
    "Marża katalogowa pozycji",
        FaktSprzedaz[Ilosc] * RELATED ( DimProdukty[MarzaNetto] )
)

Krok 3. Na karcie Modelowanie kliknij Nowa tabela, wklej formułę z ramki wyżej i zatwierdź. Przejdź do widoku Dane, wybierz tabelę Podgląd marży pozycji i zwiń panel Dane strzałkami w jego prawym górnym rogu, żeby na ekranie zmieściło się jak najwięcej kolumn. Ostatnia z nich to Marża katalogowa pozycji, dodana przez ADDCOLUMNS.

Ta tabela obliczana (utworzona przez „Nowa tabela" na wstążce Modelowanie) pokazuje pięć transakcji o najwyższej wartości netto wraz z policzoną marżą katalogową pozycji: wszystkie pięć to ten sam produkt sprzedany po dziesięć sztuk, więc w każdym wierszu jest 2 530 zł, czyli dziesięć razy 253 zł marży jednostkowej. Widząc konkretne liczby dla pojedynczych wierszy, natychmiast wychwycisz, czy formuła mnoży to, co trzeba, i czy nie ma pomyłki w kolumnach. Po debugowaniu tabelę usuwasz: prawy przycisk na tabeli w panelu Dane, Usuń z modelu. W produkcyjnym modelu nie jest potrzebna i w pliku efektu jej nie ma.

Podsumowanie: iteratory w praktyce Nordvelli

Zebraliśmy komplet narzędzi, które odróżniają początkującego od świadomego autora miar DAX. Iteratory z rodziny X liczą wyrażenie wiersz po wierszu i agregują wynik, dzięki czemu policzysz marżę katalogową, iloczyn dwóch kolumn czy wartość zależną od warunku, których zwykły SUM nie policzy. Zmienne VAR i RETURN zamieniają nieczytelny łańcuch nawiasów w przepis, który da się przeczytać od góry do dołu i zdebugować. DIVIDE chroni Cię przed Nieskończonością, która wychodzi z dzielenia ukośnikiem, gdy mianownik jest pusty. A technika „zwróć jedną zmienną" pozwala precyzyjnie namierzyć, który fragment miary się myli.

Poniższa tabela zbiera decyzję, którą będziesz podejmować przy każdej nowej mierze Nordvelli: kiedy iterator, a kiedy zwykła agregacja. Trzymaj się jej, a Twoje miary będą jednocześnie poprawne i wydajne.

W kolejnym rozdziale o inteligencji czasowej połączymy iteratory ze zmiennymi i funkcjami dat, żeby policzyć narastająco od początku roku oraz porównać sprzedaż z tym samym okresem roku poprzedniego. Zobaczysz wtedy, że VAR i DIVIDE, które poznajesz tutaj, są chlebem powszednim każdej dobrej miary czasowej.

✕