Power BI: les 7 van 9, CALCULATE, budget en vorige maand
CALCULATE in DAX: filters, budget en vorige maand
Noor wil nu meer weten dan alleen de omzet. Hoeveel kwam er van de Horeca? Zitten we boven of onder budget? En hoe doen we het tegenover vorige maand? Voor al die vragen gebruik je in DAX dezelfde functie: CALCULATE. In deze les bouw je daarmee vier nieuwe metingen en vergelijk je je cijfers met het budget.
CALCULATE: een meting met een extra filter
Je kent de meting Omzet uit les 6. CALCULATE pakt zo'n meting en zet er een extra filter omheen. Het eerste onderdeel is wat je wilt uitrekenen. Daarna komen de filters die je erbij wilt hebben. Zo maak je de omzet van alleen de Horeca:
Omzet Horeca = CALCULATE([Omzet], Klanten[Klanttype] = "Horeca")Maak de meting zoals in les 6: kies Nieuwe meting (New measure) en typ de formule. Zet hem in een kaart (Card). Je ziet nu € 9.268,30. Dat is precies de Horeca-regel uit de tabel van les 6.
Vergelijken met budget
Bonenberg heeft ook een budget: een omzetdoel per maand en per categorie. Dat staat in budget.xlsx. Het budget heeft een ander detailniveau dan de verkopen. Een verkoopregel gaat over één product, het budget over een hele categorie. Daarom koppel je het niet direct aan de verkopen. Je zet er een kleine tabel met categorieën tussen.
Probeer het zelf
- Laad
budget.xlsxzoals je dat in les 2 met Gegevens ophalen (Get data) deed. Controleer in Power Query dat Maand een datum is en Budget een getal. - Maak de kleine tabel Categorieën. Kies in het lint op het tabblad Modellering (Modeling) voor Nieuwe tabel (New table) en typ
Categorieën = DISTINCT(Producten[Categorie]). - Open de weergave Model. Kies Relaties beheren (Manage relationships) of sleep de velden. Maak drie relaties: Categorieën[Categorie] naar Producten[Categorie], Categorieën[Categorie] naar Budget[Categorie] en Datum[Datum] naar Budget[Maand]. Alle drie zijn één-op-veel (1:*) met filterrichting Enkel (Single), van de kleine tabel naar de grote.
- Maak de meting
Budget = SUM(Budget[Budget])en de metingVerschil budget = [Omzet] - [Budget]. - Maak een tabelvisual met Categorieën[Categorie], Omzet, Budget en Verschil budget.
Budget = SUM(Budget[Budget])Verschil budget = [Omzet] - [Budget]Veelgemaakte fout: de verkeerde categorie in je visual
Gebruik in een visual met budget altijd Categorieën[Categorie] en nooit Producten[Categorie]. Het budget is alleen gekoppeld aan de kleine tabel. Neem je de categorie uit Producten, dan krijg je bij elke categorie hetzelfde totale budget te zien of een leeg budget.
Het budgetbestand bevat ook april. Zolang je alleen januari tot en met maart hebt geladen, staat april dus al wel in het budget, maar nog niet in de omzet. Filter je visual op de eerste drie maanden als je de controlewaarden hieronder wilt nalopen. Over alle vier de maanden is het budget € 23.350,00.
Omzet per maand budget
Omzet per categorie
Geen filter. Klik op een knop, een maand of een categorie.
De gestippelde lijn is het budget per maand. Klik op een maand of categorie om te filteren.
Vorige maand en cumulatief
Twee metingen vragen om tijd, dus om je datumtabel. DATEADD schuift de datums in het filter op, bijvoorbeeld een maand terug. TOTALYTD (year to date, vanaf begin van het jaar) telt op tot en met de huidige datum.
Omzet vorige maand = CALCULATE([Omzet], DATEADD(Datum[Datum], -1, MONTH))Omzet cumulatief = TOTALYTD([Omzet], Datum[Datum])Zet deze twee metingen in een tabelvisual met de meting Omzet en een maand uit je datumtabel. Je datumtabel uit les 5 heeft nog alleen losse dagen. Maak er daarom twee kolommen bij: kies in de Tabelweergave de tabel Datum, dan Nieuwe kolom (New column), en typ Maand = FORMAT(Datum[Datum], "mmm"). Maak daarna nog een nieuwe kolom MaandNr = MONTH(Datum[Datum]). Staan de maanden op alfabet (apr, feb, jan, mrt)? Selecteer dan de kolom Maand, kies in het lint Sorteren op kolom (Sort by column) en kies MaandNr. Hoe de maandnamen eruitzien (jan of Jan, Nederlands of Engels) hangt af van de taalinstellingen van je bestand.
De datumtabel moet gemarkeerd zijn met Markeren als datumtabel (Mark as date table), zoals je in les 5 deed, en gekoppeld zijn aan Verkopen[Orderdatum]. Anders geven DATEADD en TOTALYTD een fout of een verkeerd getal.
Klopt het? Controleer je tabellen
| Maand | Omzet | Budget | Verschil |
|---|---|---|---|
| januari | € 5.516,35 | € 5.850,00 | € -333,65 |
| februari | € 4.486,95 | € 4.200,00 | € 286,95 |
| maart | € 6.754,80 | € 7.000,00 | € -245,20 |
| Categorie (jan t/m mrt) | Omzet | Budget | Verschil |
|---|---|---|---|
| Bonen | € 12.236,85 | € 12.650,00 | € -413,15 |
| Gemalen | € 1.415,20 | € 1.300,00 | € 115,20 |
| Cups | € 590,75 | € 600,00 | € -9,25 |
| Accessoires | € 2.515,30 | € 2.500,00 | € 15,30 |
Samen komt dat uit op een budget van € 17.050,00 tegenover een werkelijke omzet van € 16.758,10. Bonen zit duidelijk onder budget, de rest ligt dicht bij het doel. Bij de meting Omzet vorige maand is de cel in de rij januari leeg, want er is geen december in de gegevens.
Onthoud
- CALCULATE rekent een meting uit met een extra of aangepast filter, zoals Klanttype = "Horeca".
- Budget heeft een ander detailniveau. Koppel het via de tabel Categorieën en via Datum.
- Gebruik in visuals met budget Categorieën[Categorie], nooit Producten[Categorie].
- DATEADD en TOTALYTD hebben een gemarkeerde datumtabel nodig.
Check jezelf
Wat doet CALCULATE?
Het rekent een meting uit met een extra of aangepast filter.
Welke categorie gebruik je in een visual met budget en omzet?
Categorieën[Categorie], uit de kleine tabel die aan zowel Producten als Budget gekoppeld is.
Waarom heb je een datumtabel nodig voor Omzet vorige maand?
DATEADD werkt met een gemarkeerde datumtabel zonder gaten, gekoppeld aan de orderdatum.
Ken je iemand die dit ook kan gebruiken? Deel de cursus.
Veelgestelde vragen
Wat doet CALCULATE in DAX?
CALCULATE rekent een meting uit en voegt daar een extra filter aan toe, of past het filter aan. Bijvoorbeeld de omzet, maar alleen voor klanten van het type Horeca. Het is een van de belangrijkste functies in DAX.
Waarom heb je een datumtabel nodig voor vorige maand?
Functies als DATEADD en TOTALYTD kijken naar datums in een aparte datumtabel zonder gaten. Die tabel moet je markeren als datumtabel (Mark as date table) en koppelen aan de datum in je verkopen. Zonder die tabel geven deze functies een fout of een verkeerd getal.
Hoe vergelijk je werkelijke cijfers met budget in Power BI?
Je laadt het budget als aparte tabel, koppelt die via een kleine tabel met categorieën en via de datumtabel aan je model, en maakt een meting Budget. Daarna is het verschil een eenvoudige aftrekking: omzet min budget.
Wat is TOTALYTD?
TOTALYTD telt de uitkomst van een meting op vanaf het begin van het jaar tot en met de datum in de rij. In de rij maart staat dus de omzet van januari, februari en maart samen.