Messi, Ronaldo, Van Persie en DAX

Aan de hand van een kleine dataset ga ik toelichten hoe je in PowerBI (met behulp van DAX) berekeningen kunt doen die mogelijk interessant zijn voor analyse. Voor mensen met enige programmeerkennis kan dit helpen om te starten met eigen calculaties in PowerBI. De volgende aspecten worden behandeld:
• De VAR (nee, niet de Video Assistant Referee)
• Kolommen op rijbasis aggregeren
• Converteren van strings naar getallen
• Programmeren onafhankelijk van de sortering

De dataset is samengesteld op basis van Wikipediagegevens van drie voetballers: Lionel Messi, Cristiano Ronaldo en Robin van Persie. Als startpunt beschikken we per seizoen (t/m seizoen 2017-2018) over de competitie(s) waarin ze actief waren en het aantal goals dat ze maakten.
De bovenstaande aspecten worden behandeld aan de hand van de volgende berekeningen:
• Toename/afname van het aantal doelpunten ten opzichte van het vorige seizoen
• Totaal aantal doelpunten van een speler na ieder seizoen

Startpunt dataset

data1

Toename/afname van het aantal goals ten opzichte van het vorige seizoen
Voor analyse is het vaak waardevol om prestaties te kunnen vergelijken met het voorgaande jaar. In ons geval willen we weten hoeveel goals een speler meer (of minder) heeft gemaakt in een seizoen dan in het voorgaande seizoen. Dit gaan we berekenen in drie stappen:
• Totaal aantal goals dit seizoen
• Totaal aantal goals vorig seizoen
• Toename/afname aantal goals

Totaal aantal goals dit seizoen
Aangezien spelers in meerdere competities kunnen uitkomen in één seizoen (bijvoorbeeld bij een transfer in de winterstop) gaan we eerst het totale aantal goals per seizoen uitrekenen. In PowerBI kan dit als volgt:

Goals Seizoen = 
VAR
    Speler = [Speler]
VAR
    Seizoen = [Seizoen]
RETURN
SUMX (
    FILTER (
        'Dataset',
        [Speler] = Speler
            && [Seizoen] = Seizoen
    ),
    [Goals]
)

Deze code berekent voor iedere rij een getal en plaatst deze bij de desbetreffende rij in de kolom ‘Goals Seizoen’. In dit geval zijn dat 51 berekeningen. Per berekening worden de variabelen ‘Speler’ en ‘Seizoen’ bepaald, 51 keer dus. Vervolgens gaat de sumx voor iedere berekening door alle rijen heen en somt de waarde in ‘Goals’ op als aan de condities in rij 10 en 11 wordt voldaan. In totaal kijken we dus naar 51*51 rijen. ‘ Het resultaat (gedeeltelijk):

data2

Aan de hand van de de waarde voor ‘Goals Seizoen’ van 7 in rij 4 licht ik de berekening toe: ‘VAR Speler’ neemt de waarde aan van de Speler waarvoor we in deze rij de berekening maken: “Messi”. ‘VAR Seizoen’ neemt de waarde aan van “2004-2005”. In feite is de code voor deze rij als volgt:

Goals Seizoen = 
SUMX (
    FILTER (
        'Dataset',
        [Speler] = "Messi"
            && [Seizoen] = "2004-2005"
    ),
    [Goals]
)

Voor iedere rij in de dataset wordt gekeken of de waarde voor ‘Speler’ gelijk is aan “Messi” (dat is zo voor rij 1 t/m 17) en of de waarde voor ‘Seizoen’ gelijk is aan “2004-2005” (dat is zo voor rij 3 en 4). Dus worden de waarden voor ‘Goals’ in rij 3 (6) en rij 4 (1) bij elkaar opgeteld: 7.

Het gebruik van variabelen (met VAR) maakt het schrijven van DAX code een stuk eenvoudiger. Daarnaast wordt je code beter leesbaar en herbruikbaar. Daarnaast heb je hierdoor de functie ‘Earlier’, volgens DAX-expert Alberto Ferrari de meest gehate DAX-functie, niet meer nodig.

Totaal aantal goals vorig seizoen
Dit berekenen we op vergelijkbare wijze als het totale aantal goals van dit seizoen. Enige verschil is dat we nu het aantal goals willen optellen van de rijen waarbij het seizoen een jaar kleiner is dan in de rij waar we de waarde willen hebben staan. Het feit dat het seizoen gegeven is als een reeks van vier getallen, gevolgd door een streepje en nogmaals vier getallen maakt dit enigszins ingewikkeld. PowerBI interpreteert dit als een tekstveld. We kunnen de vergelijking alsnog als volgt maken:

Goals Vorig Seizoen = 
VAR
    Speler = [Speler]
VAR
    SeizoenGetal = INT ( LEFT ([Seizoen] , 4 ) )
Return
SUMX (
    FILTER (
        'Dataset',
        [Speler] = Speler
            && INT ( LEFT ( [Seizoen], 4 ) ) + 1
                = SeizoenGetal
    ),
    [Goals]
)

‘VAR SeizoenGetal’ neemt de waarde aan van de rij waarvoor je momenteel de waarde aan het berekenen bent: voor rij 1 zou dit “2003-2004” zijn, een tekstveld. De functie ‘LEFT’ met parameter 4 pakt hier de eerste vier tekens van: “2003”, een tekstveld. De functie ‘INT’ maakt hier een getal van: 2003, klaar om vergeleken te worden met een ander getal. Voor iedere rij in de dataset wordt gekeken of de waarde voor de eerste vier tekens van ‘Seizoen’, uitgedrukt als getal, precies 1 kleiner is dan 2003 (of de waarde voor de rij waarvoor je een som aan het uitrekenen bent). Dan is het seizoen namelijk een jaar kleiner dan in de rij waarvoor we een som aan het uitrekenen zijn. Voor rij 1 geldt deze conditie voor rij 18 en rij 35. Hier is de speler echter niet gelijk aan “Messi”, de waarde die in rij 1 voor ‘VAR Speler’ aangenomen wordt. Dus zijn er voor rij 1 geen rijen die aan beide condities voldoen, en laten we het veld leeg:

data3

Toename/Afname aantal goals
Nu we per speler per seizoen het aantal goals van dit seizoen en het aantal goals van vorig seizoen hebben, is het berekenen van de toe- of afname een fluitje van een cent: we pakken het aantal goals van dit seizoen min het aantal goals van vorig seizoen.

Toename/Afname Aantal Goals = 
[Goals Seizoen] - [Goals Vorig Seizoen]

Het resultaat hier kan voor discussie zorgen: Wil je voor het eerste seizoen wel of geen waarde voor de toename? Je hebt namelijk geen vorig seizoen om mee te vergelijken.

data4

Als je het eerste veld ongevuld wilt laten, kan dat door in de definitie van de kolom een clausule op te nemen. Hierdoor wordt de waarde alleen gevuld als er een waarde staat in ‘Goals Vorig Seizoen’. In DAX doe je dit met een IF en een ISBLANK statement:

Toename/Afname Aantal Goals2 =
IF (
    ISBLANK ( [Goals Vorig Seizoen] ),
    BLANK (),
    [Goals Seizoen] - [Goals Vorig Seizoen]
)

Met als resultaat:

data5

Totaal aantal doelpunten van een speler na ieder seizoen
Nu gaan we per seizoen per speler berekenen hoeveel goals de speler in totaal in zijn loopbaan na dat seizoen had gemaakt. Met DAX kan dit als volgt:

Goals Carriére = 
VAR
    Speler = [Speler]
VAR
    Seizoen = [Seizoen]
RETURN
SUMX (
    FILTER (
        'Dataset',
        [Speler] = Speler
            && [Seizoen] <= Seizoen
    ),
    [Goals]
)

De logica is grotendeels gelijk aan de berekening voor het aantal goals per seizoen. Het enige verschil is dat we nu sommeren over alle rijen met dezelfde speler waarin het seizoen gelijk is aan of is kleiner is dan het seizoen in de rij waar we een som voor berekenen.
Je vraagt je misschien af waarom we het seizoen niet hoeven te converteren naar een getal. Dit komt doordat DAX strings alfabetisch kan vergelijken. Bijvoorbeeld, de vergelijking “Alfa” < “Bravo” evalueert naar ‘True’; “2003-2004” < “2004-2005” dus ook.
Benoemenswaardig is nog het feit dat de sortering van de dataset bij alle berekeningen geen rol speelt. Ook als de spelers en de seizoenen door elkaar staan, werken alle bovenstaande formules. Dit is krachtig, hierdoor kun je nieuwe rijen toevoegen zonder dat je rekening hoeft te houden met de positie waar je ze toevoegt. Als afsluiter volgt nog de volledige dataset, met berekende kolommen.

Dataset met berekende kolommen

data eind

Geef een reactie

Gelieve met een van deze methodes in te loggen om je reactie te plaatsen:

WordPress.com logo

Je reageert onder je WordPress.com account. Log uit /  Bijwerken )

Google photo

Je reageert onder je Google account. Log uit /  Bijwerken )

Twitter-afbeelding

Je reageert onder je Twitter account. Log uit /  Bijwerken )

Facebook foto

Je reageert onder je Facebook account. Log uit /  Bijwerken )

Verbinden met %s