Bij het analyseren van grote hoeveelheden gegevens zijn we vaak geïnteresseerd in trends en correlaties. Een van de meest basale methoden om dit te onderzoeken is lineaire regressie. Lineaire regressie is een statistische methode om de correlatie tussen verschillende variabelen in een dataset te bepalen. Recent zijn er nieuwe DAX-functies geïntroduceerd die mogelijkheden voor lineaire […]
Bij het analyseren van grote hoeveelheden gegevens zijn we vaak geïnteresseerd in trends en correlaties. Een van de meest basale methoden om dit te onderzoeken is lineaire regressie. Lineaire regressie is een statistische methode om de correlatie tussen verschillende variabelen in een dataset te bepalen. Recent zijn er nieuwe DAX-functies geïntroduceerd die mogelijkheden voor lineaire regressie bieden. In deze blogpost duiken we in deze nieuwe functies: LINEST en LINESTX.
Om het concept van lineaire regressie te illustreren, heb ik een dataset met auto’s in Nederland geladen. Veel voertuiggegevens zijn beschikbaar via open datasets. De prijzen van geregistreerde voertuigen zijn beschikbaar voor ongeveer 8 miljoen voertuigen die sinds 2004 zijn geregistreerd en nog steeds op de weg rijden. De onderstaande grafiek toont de gemiddelde voertuigprijs per registratiedatum. Ik heb een trendlijn toegevoegd via de analyse-opties van het kolomdiagram:
De trendlijn geeft een ruwe indicatie van de groei van de gemiddelde prijs door de jaren heen. Deze is bepaald via eenvoudige lineaire regressie op één variabele; in dit geval het jaar.
In wiskundige termen bestaat de uitdaging bij lineaire regressie uit het vinden van een functie:
zodat de lijn gedefinieerd door deze functie zo dicht mogelijk bij de verzameling (x, y)-paren ligt. De meest gebruikte methode om deze functie te vinden is de kleinste-kwadratenmethode, die de waarden a en b bepaalt waarvoor de totale fout minimaal is. Voor elke (x, y)-waarde is er een verschil tussen y en F(x): de fout. Omdat F(x) groter of kleiner dan y kan zijn, wordt dit probleem opgelost door te werken met de kwadratische vorm:
Voor eenvoudige lineaire regressie bestaan exacte wiskundige uitdrukkingen voor a en b. Dit betekent dat dit probleem in DAX kan worden opgelost in elk Power BI-model. In ons boek Extreme DAX beschrijven we hoe dit kan worden gedaan. Wanneer je echter lineaire regressie op meer dan één variabele wilt toepassen, wordt het lastiger: de enige oplossing is dan een algoritme gebruiken om de functie F(x) te benaderen.
De nieuwe DAX-functies LINEST en LINESTX doen precies dat. Dit betekent dat we nu eenvoudige functies hebben om lineaire regressie uit te voeren in Power BI. Ik zal dit demonstreren met een eenvoudig datamodel dat voertuigregistraties bevat.
De functies LINEST en LINESTX hebben gegevenspunten nodig, oftewel (x, y)-paren, en ze geven een uniek type uitvoer, wat een primeur is in DAX. Logischerwijs zou de uitvoer de functie F(x) moeten zijn, maar DAX ondersteunt dat momenteel niet. In plaats daarvan retourneert de functie een één-rij-tabel met de bouwstenen van F(x), samen met extra informatie over de lineaire regressie. Met deze gegevens kun je zelf de functie F(x) opbouwen.
Ik geef de voorkeur aan de functie LINESTX, omdat deze je ultieme controle geeft over de gebruikte gegevenspunten. LINESTX neemt een tabel als eerste argument, waarbij elke rij een datapunt definieert voor de regressie. Het tweede argument specificeert de y-waarde, en andere argumenten geven de x-waarden. Om te beginnen met eenvoudige lineaire regressie op het jaar, heb ik een berekende tabel gemaakt met de volgende formule:
Est Price (Year) =
LINESTX(
DISTINCT(‘Calendar'[Year]),
CALCULATE(AVERAGE(fRegistration[Price])),
‘Calendar'[Year]
)
De formule neemt de verschillende jaarwaarden uit de Calendar-tabel, berekent de gemiddelde prijs van voertuigen per jaar (y-waarden) en de jaartallen zelf (x-waarden). Het resultaat is de volgende tabel (verkort voor leesbaarheid):
De kolommen Slope1 en Intercept zijn het belangrijkst om de functie F(x) op te bouwen. In onze eerder gebruikte wiskundige formule komt a overeen met Slope1, terwijl b de Intercept is. Een DAX-maatstaf om de regressielijn te berekenen kan als volgt worden gemaakt:
Price (Regression Year) =
VAR ThisYear = SELECTEDVALUE(‘Calendar'[Year])
VAR Intercept = MIN(‘Est Price (Year)'[Intercept])
VAR Slope = MIN(‘Est Price (Year)'[Slope1])
RETURN
Slope * ThisYear + Intercept
Het resultaat lijkt op de trendlijn, maar nu kunnen we natuurlijk de maatstaf gebruiken om de gemiddelde prijs voor bijvoorbeeld 2024 te schatten.
Het gebruik van alleen het jaar als variabele om de voertuigprijs te schatten, geeft uiteraard een zeer ruwe schatting. Daarom is het interessant om extra variabelen toe te voegen. De onderstaande formule neemt bijvoorbeeld ook de lengte van een voertuig mee:
Est Price 2 =
LINESTX(
SUMMARIZE(fRegistration, ‘Calendar'[Year], Length[Length]),
CALCULATE(AVERAGE(fRegistration[Price])),
‘Calendar'[Year],
Length[Length]
)
Hier gebruiken we SUMMARIZE om alle unieke combinaties van jaar en voertuiglengte als basistabel te verkrijgen. Let op: de functie LINESTX kan veel rekentijd kosten bij grote datasets. Overweeg daarom bijvoorbeeld om lengtecategorieën te maken in plaats van elke unieke lengte afzonderlijk te gebruiken.
Omdat we nu twee x-variabelen hebben (jaar en lengte), hebben we ook twee Slope-waarden. De bijbehorende maatstaf wordt:
Price (Regression Year – Length) =
VAR ThisYear = SELECTEDVALUE(‘Calendar'[Year])
VAR ThisLength = SELECTEDVALUE(Length[Length])
VAR Intercept = MIN(‘Est Price 2′[Intercept])
VAR SlopeYear = MIN(‘Est Price 2′[Slope1])
VAR SlopeLength = MIN(‘Est Price 2′[Slope2])
RETURN
SlopeYear * ThisYear + SlopeLength * ThisLength + Intercept
Dit definieert een functie die er geweldig uit zou zien in een 3D-grafiek, maar die ook kan worden gevisualiseerd met standaardvisualisaties en filters. Hieronder is de regressielijn op voertuiglengte te zien voor het jaar 2021:
Een veelvoorkomend misverstand is dat lineaire regressie altijd resulteert in een rechte lijn. Het lineaire aspect betekent echter dat de relatie tussen de functie F(x) en de invoervariabelen lineair is. Dit betekent niet dat je geen niet-lineaire variabelen kunt gebruiken!
Omdat de werkelijke gemiddelde prijs van voertuigen over de jaren heen geen perfecte rechte lijn vormt, kunnen we LINESTX gebruiken met het jaar en het kwadraat van het jaar als invoervariabelen:
Est Price X2 =
LINESTX(
ADDCOLUMNS(
DISTINCT(‘Calendar'[Year]),
“X2”, ‘Calendar'[Year] ^ 2
),
CALCULATE(AVERAGE(fRegistration[Price])),
‘Calendar'[Year],
[X2]
)
De bijbehorende DAX-maatstaf wordt:
Price (Regression X2) =
VAR ThisYear = SELECTEDVALUE(‘Calendar'[Year])
VAR Intercept = MIN(‘Est Price X2′[Intercept])
VAR SlopeX = MIN(‘Est Price X2′[Slope1])
VAR SlopeX2 = MIN(‘Est Price X2′[Slope2])
RETURN
SlopeX * ThisYear + SlopeX2 * ThisYear ^ 2 + Intercept
Het resultaat van deze lineaire regressie ziet er als volgt uit:
Bij het uitvoeren van lineaire regressie is de keuze van welke variabelen te gebruiken een belangrijke beslissing. Hiermee betreed je langzaam het terrein van echte data science, waarin je weloverwogen keuzes moet maken over de variabelen om overfitting te voorkomen, de nauwkeurigheid te optimaliseren, enzovoort. Desondanks is de toevoeging van LINEST en LINESTX een spannende ontwikkeling in DAX!
