Data Analyse Breda

Power BI ranking: Top N + others filter

In deze blogpost laten we u zien hoe u een TopN met others filter kan maken. Het is nu al mogelijk om in het filtermenu van Power BI een TopN aan te geven. Dit dient echter handmatig aangepast te worden, en de totale waarden van alle andere categoriën wordt niet weergeven. In deze post laten we u zien hoe u ervoor kan zorgen dat u een dynamische TopN filter heeft, waarbij ook de som van alle overige resultaten zichtbaar is.

Voorbeeld

Hieronder hebben we een Power BI dashboard ingesloten waarbij de TopN functie duidelijk zichtbaar is. We hebben een dataset met daarin de sales gegevens van een bedrijf per land. De gebruiker kan aan de hand van de TopN filter linksboven bepalen hoeveel landen zichtbaar zijn. De ranking vindt plaats op basis van de winst in de betreffende landen. Als je 4 selecteert krijg je dus de 4 landen met de hoogste winst. De winst van de rest van de landen wordt gegroepeerd in de categorie Others.

We gaan nu door naar de stappen die u moet ondernemen om het voorbeeld na te bootsen met uw dataset. Wil u meedoen aan de hand van onze dataset, download dan het onderstaande bestand:

Stap 1: Maak een lijst van alle landen + anderen

Wij willen graag een TopN selectie maken van onze landen. Daarvoor moeten we eerst een lijst met alle unieke landen creëeren, waarbij ook de categorie Others word toegevoegd.

  • Open Power BI Desktop en laad uw databestand in.
  • Ga nu aan de linkerkant naar de dataview en selecteer boven aan het menu de optie New table.
  • Hier vul je de onderstaande code in om een lijst te maken van alle landen uit ons salesdocument, inclusief de optie others.

LandenLijst = 
DISTINCT(
    FILTER(
        UNION(
            ALLNOBLANKROW(Salesdata[Country]),
            {"Others"}),
            NOT ISBLANK(Salesdata[Country])
    )
)

Stap 2: Maak een relatie tussen de landenlijst en SalesData

We gaan nu een relatie maken tussen de net gecreëerde tabel LandenLijst en onze SalesData.

  • Ga naar model in het linkermenu.
  • Klik vervolgens op Manage Relationships.
  • Maak een nieuwe relationship aan tussen de landenlijst en de Salesdata. Ik heb ervoor gekozen om bij direction both aan te klikken.

Stap 3: Maak een TopN filter aan

We gaan nu de filter maken waarmee de gebruiker het aantal zichtbare landen kan veranderen. Zoals je misschien gezien hebt in onze dataset hebben we 8 landen tot onze beschikking. Voor dit voorbeeld wil ik er dan ook nooit meer dan 8 landen zien. Volg de onderstaande stappen voor het maken van een filter:

  • Klik bovenaan in het menu op Modelling
  • Klik nu op New Parameter
  • Geef de parameter een naam (in mijn geval TopN), en creëer een minimum van 1 en een maximum van 8.
  • Klik nu op ok, je zal nu een filter krijgen.

Stap 4: Creëeren van een ranking

We gaan de landen nu rangschikken op basis van de variabele total profit. Dit doen we aan de hand van twee measures.

  • Maak een measure aan in de tabel SalesData. Hierin gaan we aan de hand van de volgende code de totale Winst berekenen.
Winst = sum(Salesdata[Total Profit])
  • Nu gaan we aan de hand van de Winst een ranking maken. Maak een nieuwe measure aan met de volgende code:
Ranking = 

VAR Ranking = 
    RANKX(ALLSELECTED(LandenLijst[Country]),
        CALCULATE([Winst],
        NOT ISBLANK(Salesdata[Country]),
        ALLSELECTED(Salesdata[Country])
        ))
VAR isOthersGeselecteerd = SELECTEDVALUE(LandenLijst[Country])="Others"
VAR Resultaat= if(isOthersGeselecteerd,-1,Ranking)
Return Resultaat

Wat gebeurd hier precies? We maken een Ranking met behulp van de formule RankX. Bij het eerste argument moet je invullen op basis waarvan je gaat ranken. Hiervoor gebruiken we onze lijst met alle landen + others. Het tweede argument vraagt op basis van welke waarde we gaan ranken. We gebruiken hier de calculate functie. Daarin geven we aan dat we filteren op basis van onze Winst measure, hierbij brengen we nog twee filters aan op deze winst. We willen geen records zien waar geen land gespecificeerd is. Daarnaast gebruiken we ALLSELECTED, wat wil zeggen dat we alleen de landen meenemen die momenteel geselecteerd zijn. Stel dat we Nederland er uitfilteren, dan word deze waarde dus niet meegenomen in de ranking.

Vervolgens hebben we de variabele IsOtherGeselecteerd. Deze variabele zal true aangeven als Others geselecteerd is, en false als er een land geselecteerd is. Deze variabele gebruiken we vervolgens om ons resultaat te berekenen. Het if statement bij resultaat doet het volgende: Is isOthersGeselecteerd gelijk aan true, geeft deze rij dan de waarde -1, anders gebruik je de ranking.

Als je nu een tabel maakt met daarin de variabele Country uit de LandenLijst, en onze measures Winst en Ranking, dan krijg je het volgende resultaat:

Stap 5: Bepalen welke rijen zichtbaar moeten zijn

We gaan nu een measure maken die ons gaat vertellen welke rijen zichtbaar moeten zijn, en welke niet. Dit is nodig om de TopN filter goed te laten werken.

  • Maak een nieuwe measure aan met de volgende code:
RijZichtbaar = 

VAR Resultaat = INT([Ranking] <= 'TopN'[TopN Value])
Return Resultaat
  • Bij resultaat geven we aan dat als de Ranking kleiner is als de waarde van onze TopN, er een 1 toegekend moet worden. Anders zal er een 0 zichtbaar zijn.
  • Als je RijZichtbaar nu toevoegd aan je tabel, en vervolgens de TopN filter verschuift naar een getal zal je zien dat de tabel dynamisch een 1 of 0 zal weergeven. Dit werkt ook in combinatie met de landenfilter uit LandenLijst.

Stap 6: De waarde van Others berekenen.

We zijn nu bijna klaar. Echter zal het je opgevallen zijn dat Others nog geen waarde heeft. Dit gaan we nu veranderen.

  • Maak een nieuwe measure aan
  • Plak daar de volgende code in.
Winst_others = 
VAR Winst = CALCULATE([Winst], REMOVEFILTERS(LandenLijst[Country]))
RETURN
    if(
        ISINSCOPE(LandenLijst[Country]),
        VAR TopnFilter = 'TopN'[TopN Value]
        VAR IsOthersGeselecteerd = SELECTEDVALUE(LandenLijst[Country]) = "Others"
        VAR WinstLanden = ADDCOLUMNS(ALLSELECTED(LandenLijst),"@WAARDE",[Winst])
        VAR WinstBepaling = TOPN(TopnFilter,WinstLanden,[@WAARDE])
        VAR WinstTopN = SUMX(WinstBepaling,[@WAARDE])
        VAR Resultaat = if(IsOthersGeselecteerd,Winst-WinstTopN,[Winst])
        return Resultaat,
        Winst)

Wat doet deze measure.

  • IF ISINCOPE = Als het land onderdeel is van onze landenlijst gaan we de variabele Resultaat teruggeven, anders gaan we de de measure Winst weergeven.
  • VAR TopnFilter = Geeft ons de waarde van de TopN filter.
  • VAR IsOthersGeselecteerd = Geeft true als er Others word weergeven, anders false.
  • WinstLanden = Maakt een nieuwe kolom aan in de landenlijst waarin we de winst van de landen kunnen opslaan. Het eerste argument is om welke tabel het gaat, hier vullen we onze landen in, het tweede argument is de naam van de kolom, hier heb ik @WAARDE van gemaakt. Het derde argument is de waarde die toegekend moet worden aan het land uit de lijst. Hier pakken we de Winst measure.
  • WinstBepaling = Hier gaan we na wat de TopN nou eigenlijk is qua landen. Het eerste argument is hoeveel je TopN waarde moet zijn. Hier vullen we het getal uit onze filter in. Dit getal hebben we reeds teruggekregen in onze variabele TopnFilter. Het tweede argument is de tabel die er bij hoort. Hier pakken we de landenlijst. Vervolgens moeten we een kolom uit deze tabel specificeren, en we willen graag de winst, hier hebben we net de naam @WAARDE aan gegeven, dus die selecteren we.
  • WinstTopN = In winstbepaling hebben we net een lijst gemaakt van alle landen in de TopN uit onze filter, inclusief de winst. Nu willen we daar de totale som van hebben. Dit doen we met SUMX. Het eerste argument is hier opnieuw de tabel die we willen hebben, en dit is Winstbepaling, daar hebben we namelijk alleen de TopN landen staan. Vervolgens gaan we van al deze landen de @WAARDE optellen.
  • Resultaat = Hier berekenen we de winst per land. Voor others wijzen we de totale winst toe, min de winst van de TopN. Bij de rest van de landen willen we gewoon de normale winst zien.

In het onderstaande voorbeeld hebben we nu de winst_others measure toegevoegd aan de tabel. Dit ziet er zo uit:

Je ziet nu een waarde bij others. Momenteel is de filter ingesteld op 3 landen. De others waarde is dus de totale winst-de winst van deze drie landen. Als je de filter aanpast zal ook de waarde dynamisch veranderen. In de praktijk wil je natuurlijk niet de landen zien die je niet selecteert. Dit kunnen we realiseren door onze measure RijZichtbaar als filter toe te voegen aan onze tabel. Zie het onderstaande voorbeeld:

Gebruiken in grafieken!

Nu kan je deze filter gaan gebruiken in je Power BI bestand zoals wij bovenaan deze post hebben gedaan. Zorg er hier wel voor dat je de RijZichtbaar measure toevoegd en instelt op 1, anders worden de resultaten niet correct gefilterd.

Leave a Reply