W odróżnieniu od poprzedniego scenariusza, w którym wartość N w filtrze TOP N była określona statycznie w ramach formuły lub opcji filtrowania, chcemy umożliwić użytkownikowi raportu samodzielną zmianę parametru N (np. TOP 10, TOP 20, TOP 30 itd.). Z pomocą przychodzi opcja Power BI o nazwie Nowy parametr, którą można też odtworzyć w Excel Power Pivot.
Krok 1: Utwórz parametr do wyboru TOP N na raporcie Power BI lub dla tabeli przestawnej Power Pivot
Posłużmy się więc parametrem w Power BI, tworząc nowy parametr o wartości minimalnej 5, maksymalnej 100, kroku 1 i wartości domyślnej 10. Zaznaczmy też, aby pojawił się fragmentator (suwak) dla strony raportu.
Power BI posługuje się przy tym formułą tworzącą tabelę:
TOPN Klienci = GENERATESERIES(5; 100; 1)
Oraz tworzy miarę:
Wartość Parametr = SELECTEDVALUE('TOPN Klienci'[TOPN Klienci])
Jeśli ten sam proces chcemy odtworzyć w Excel Power Pivot, potrzebujemy załadować oddzielną tabelę z wyborami dla TOP N (5, 6, 7, itd.) oraz miarę analogiczną wykorzystującą formułę HASONEVALUE (opisałem jej działanie w artykule o tworzeniu przycisku do wyboru miary).
Krok 2: Utwórz miarę Sprzedaż TOP N
Nowa miara ma postać:
Sprzedaż Top N Klientów = CALCULATE([Sprzedaż total];FILTER(Klienci;RANKX(ALL(Klienci);[Sprzedaż total])<='TOPN Klienci'[Wartość Parametr]))
Krok 3: Zbuduj wykres z klientami
Wstaw wykres pokazujący Sprzedaż dla TOP N klientów. Suwakiem możesz zmieniać, ilu klientów wyświetla się na wykresie.
Czy da się zrobić dymamiczny TOP N, jeżeli dodatkowo dodamy slicery do Klientów (np. slicer wybierajacy klientów z danego regionu)?
Przy założeniu, że inne kolumny z tabeli Klienci nie będą filtrowane, formuła będzie wykorzystywać ALLEXCEPT w miejsce ALL:
Sprzedaż Top N Klientów = CALCULATE([Sprzedaż total];FILTER(Klienci;RANKX(ALLEXCEPT(Klienci;Klienci[Region]);[Sprzedaż total])<='TOPN Klienci'[Wartość Parametr]))
Formuła nie działa po wybraniu wszystkich regionów (czyli przy braku wybour na fragmentatorze), więc przydałoby się ją uzupełnić o IF z ISFILTERED.