Key Performance Indicator (KPI) maken in Excel PowerPivot

In onze vorige blog over Excel PowerPivot heb je kunnen lezen dat je met behulp van de invoegtoepassing PowerPivot gemakkelijk tabellen kunt importeren in Excel, die afkomstig zijn van verschillende bronnen. Vervolgens kun je deze tabellen gebruiken om draaitabellen te maken. Een geweldige tool om data te analyseren! Onze blog over het importeren, koppelen en analyseren van tabellen met behulp van Excel PowerPivot lees je hier.

Maar er is nog meer mogelijk met PowerPivot! In de gemaakte draaitabel kun je een KPI (Key Performance Indicator) gebruiken om totalen te vergelijken. Zo kun je bijvoorbeeld de totalen van twee jaren vergelijken om snel te zien of er beter of slechter is gepresteerd. Om de totalen te kunnen vergelijken, heb je eerst twee DAX-berekeningen nodig. DAX staat voor Data Analysis Functions. Wij maken één berekening die het totaal van het eerste jaar berekent en één berekening die het totaal van het tweede jaar berekent. Vervolgens gaan wij het totaal van het tweede jaar vergelijken met het eerste jaar via de KPI.

In deze blog leggen wij stap voor stap uit hoe je de DAX-berekeningen maakt en hoe je vervolgens via de KPI de totalen kunt bereken.

Gegevens importeren

Allereerst dien je de gewenste gegevens te importeren in Excel PowerPivot. Dit doe je met behulp van de volgende stappen:

• Wij beginnen in een leeg werkboek in Excel.
• Zorg ervoor dat het tabblad PowerPivot aanwezig is. Indien dit niet geval is, dan moet de invoegtoepassing PowerPivot geactiveerd worden via Bestand – Opties – Invoegtoepassingen – COM invoegtoepassingen.

• Klik via het tabblad PowerPivot op Beheren.

• Het PowerPivot-scherm start op. Klik nu op de knop Uit een andere bron.

• Klik op Excel-bestand en kies het gewenste bestand.

• Wanneer je op de knop Volgende klikt, kun je de aanwezige tabel kiezen.

• Hierna klik je op Voltooien en dan Sluiten.

De gegevens van de tabel zijn geïmporteerd, maar zijn ook dynamisch aan het bronbestand gekoppeld. Via de knop Vernieuwen in de tab Start van het PowerPivot-scherm kunnen de data worden geactualiseerd, wanneer data in de brontabel worden gewijzigd.

DAX-berekeningen maken

Nu de gegevens zijn geïmporteerd, gaan wij aan de slag met de DAX-berekeningen. Met behulp van deze berekeningen meten wij de totale omzet in de twee verschillende jaren. Voor de berekeningen volg je de volgende stappen:

• Ga via de taakbalk terug naar de geopende werkmap (het PowerPivot-scherm mag geopend blijven).
• Vervolgens klik je via het tabblad PowerPivot op Metingen en kies je voor de optie Nieuwe Meting.
• Wij rekenen eerst uit wat de omzet is geweest in het jaar 2021. Dit doen wij met behulp van de functie Calculate. Deze functie heeft als eerste argument de expressie. De expressie is hier de som van “Gross Sales”. Verder wordt hier gefilterd op het jaar 2021 en vergeleken met het jaar uit het veld “Date”.

• Klik op OK om de meting te bewaren.
• Op dezelfde manier berekenen wij de omzet voor het jaar 2022.

KPI maken

Wanneer de totale omzet voor de twee jaren is berekend, is het tijd om een KPI te maken.

• Klik via het tabblad PowerPivot op KPI en kies voor Nieuwe KPI. De nieuwe KPI stel je als volgt in:
  • KPI-basisveld (waarde): Omzet 2022
  • Meting: Omzet 2021
  • Statusdrempels: 100% en 200%

• Klik op OK.
• Nu maak je een draaitabel via Invoegen – Draaitabel – Vanuit Gegevensmodel. Deze draaitabel stel je als volgt in:
  • Month Number plaats je bij Rijen
  • Omzet 2021, Omzet 2022 en Status Omzet 2022 plaats je bij Waarden

De kolom Status omzet 2022 kan als volgt worden uitgelegd: wanneer de omzet in een specifieke maand in 2022 groter of gelijk is aan 150% van de omzet van diezelfde maand in 2021, dan is de indicator groen. Als de omzet in een specifieke maand in 2022 tussen de 100% en 150% is van de omzet van diezelfde maand in 2021, dan is de indicator oranje. Is de omzet van een maand in 2022 lager dan de overeenkomende maand in 2021, dan is de indicator rood.

In bovenstaand voorbeeld zie je dat je met behulp van het maken van een KPI in Excel PowerPivot in één oogopslag cijfers kunt vergelijken. Wil je meer leren over de mogelijkheden van Excel PowerPivot voor het analyseren van jouw gegevens? Volg dan onze training Excel PowerPivot Basis of Excel PowerPivot Gevorderd | DAX!