Een Project Dashboard Maken in Excel – deel 2/3

In het vorige blog heb je de eerste versie van een projectplanning dashboard gemaakt. Dat dashboard bevatte een visualisatie die antwoord gaf op de vraag: wanner start een project en hoelang duurt het?

In dit blog ga je de visualisatie verrijken met de status van een project: hoeveel dagen zijn er nog te gaan in een project, is een project al voltooid of is de deadline overschreden en zo ja, hoeveel dagen al?

Hieronder zie je wat je gaat maken. Het Excel bestand kun je hier downloaden: Project Dashboard

Gegevensopslag

Nu je de status van een project wilt weergeven, heb je per project extra informatie nodig in de gegevensbron – je moet immers weten of een project voltooid is of niet. Voeg daarom een nieuwe kolom toe aan de tabel op het eerste tabblad.

Geef kolom de volgende naam: [Voltooid] en kopieer onderstaande regels in de tabel.

Projectnaam	Taakbeschrijving	Startdatum	Einddatum	Voltooid
Project 1	Taak 1	31-Oct-20	07-Nov-20	WAAR
Project 1	Taak 2	01-Nov-20	05-Nov-20	WAAR
Project 1	Taak 3	03-Nov-20	15-Nov-20	WAAR
Project 2	Taak 1	05-Nov-20	12-Nov-20	ONWAAR
Project 2	Taak 2	07-Nov-20	14-Nov-20	ONWAAR
Project 3	Taak 1	07-Nov-20	18-Nov-20	ONWAAR
Project 3	Taak 2	07-Nov-20	15-Nov-20	ONWAAR
Project 3	Taak 3	08-Nov-20	13-Nov-20	ONWAAR
Project 3	Taak 4	09-Nov-20	13-Nov-20	ONWAAR
Project 4	Taak 1	11-Nov-20	24-Nov-20	ONWAAR
Project 4	Taak 2	11-Nov-20	20-Nov-20	ONWAAR
Project 5	Taak 1	12-Nov-20	16-Nov-20	ONWAAR
Project 5	Taak 2	13-Nov-20	20-Nov-20	ONWAAR
Project 5	Taak 3	13-Nov-20	18-Nov-20	ONWAAR

Je ziet dat de nieuwe deze alleen twee mogelijke waarden bevat:

• WAAR = een project is voltooid
• ONWAAR = een project is nog niet voltooid

Dit datatype is beter bekend als logische waarde. Omdat een logische waarde slechts twee waarden kent, WAAR of ONWAAR, fungeert het als een aan/uit schakelaar. En dit komt in heel veel scenario’s goed van pas, zoals gegevensvalidatie.

Gegevensvalidatie toepassen

Om een correcte invoer van de nieuwe kolom te garanderen kun je gegevensvalidatie aan de kolom toevoegen. Selecteer eerst alle regels in kolom [Voltooid] en ga dan via ‘Gegevens’ naar ‘Gegevensvalidatie’:

Dit brengt je in het Gegevensvalidatiescherm waar je een de regels voor data invoer kunt bepalen.

Kies onder ‘Toestaan:’ voor de laatste optie ‘Aangepast’. Nu kun je je eigen formule gebruiken voor de validatie. Let op: de formule die je schrijft moet altijd een logische waarde teruggeven:

• WAAR = validatie geslaagd
• ONWAAR = validatie mislukt

De formule die je gebruikt is =ISLOGISCH(F5). Deze formule inspecteert of de waarde in de geselecteerde cel (in dit geval F5) een logische waarde bevat.

Druk op OK om de validatieregel toe te passen.

Wanneer je nu een waarde invoert anders dan een logische waarde, dan toont Excel een waarschuwing:

Een keuzelijst maken

Hoewel deze validatieregel goed werkt is het niet zo gebruiksvriendelijk. Veel gebruikers weten niet wat logische waarden zijn, hoewel ze deze onbewust vaak toepassen. Een betere optie is om de gebruiker een keuzelijst te geven zoals je hieronder kunt zien:

Om een keuzelijst te maken moet je eerst de items van je keuzelijst in een bereik plaatsen. Pas vervolgens de gegevensvalidatie aan naar de optie ‘Lijst’ en verwijs je naar het bereik waarin de logische waarden hebt gezet:

Voordat je doorgaat is het een goed idee om te controleren of de nieuwe kolom goed is ingeladen in het datamodel. Open Power Pivot en druk op de knop Vernieuwen als je de kolom niet ziet:

Berekeningen

In het vorige deel heb je een aantal basismetingen aan het Power Pivot datamodel toegevoegd:

• Dag:= MIN( Agenda[Date] )
• Start:= MIN( Projecten[Startdatum] )
• Eind:= MAX( Projecten[Einddatum] )
• Taak: = IF( [Dag] >= [Start] && [Dag] <= [Eind], 1, BLANK() )

De laatste meting gebruikte je in de draaitabel om met voorwaardelijke opmaak de opvulkleur van de cel bepalen zodat aaneengesloten cellen een horizontale balk vormden.

Om de status van een project te visualiseren borduur je voort op hetzelfde principe.

Per status ga je een apart cijfer berekenen, en dat cijfer gebruik je in de voorwaardelijke opmaak om de status telkens een andere kleur te geven. Op die manier wordt het voor de gebruiker in één oogopslag duidelijk hoe de zaken ervoor staan:

De doelstelling is dus om een nummering te maken van de verschillende statussen, zoals hieronder weergegeven:

1. Gedaan: dagen van project die in het verleden liggen
2. Te doen: dagen in het project die in de toekomst liggen
3. Te laat: dagen in het verleden sinds de einddatum van het project liggen

Die nummering gebruik je later om te coderen naar de volgende kleuren:

1. Gedaan = Groen
2. Te doen = Lichtgroen
3. Te laat = Donkerrood

Uiteindelijk komt je overzicht er dus zo uit te zien:

Extra basis berekeningen

Om de status per project te kunnen bepalen heb je extra berekeningen nodig. Je moet kunnen bepalen of een project voltooid is of niet. Ook moet je weten of een bepaalde dag in de draaitabel tot het verleden of tot de toekomst behoort.

Neem de volgende basismetingen over. Volg daarbij weer de volgende stappen:

1. Kies eerst de brontabel waaraan je de berekening wilt koppelen
2. Daarna geef je de meting een nieuwe naam
3. Neem daarna de formule over
4. Kies tot slot de juiste opmaakoptie
5. NIEUW: Controleer of de formule een juist resultaat teruggeeft

Met behulp van COUNTROWS() kun je het aantal regels bepalen van een bepaalde tabel. Deze functie wordt in [Aantal taken] toegepast op de Projecten tabel. Door de filtercontext ‘ziet’ iedere cel in de draaitabel een andere set regels uit de Projecten tabel en retourneert deze meting dus telkens een ander resultaat.

De meting [Aantal voltooide taken] maakt gebruik van een hele handige Power Pivot functie: FILTER(). Hiermee kun je extra filters toepassen op een tabel binnen de huidige filtercontext. FILTER() kijkt voor iedere rij in de huidige filtercontext of de expressie in het tweede argument WAAR is, en retourneert een tabel met alleen díe regels.

Om de werking van [Aantal taken] en [Aantal voltooide taken] beter te begrijpen kun je ze naast elkaar in een aparte draaitabel zetten:

Je ziet dat Project 1 uit drie taken bestaat. Twee daarvan zijn al voltooid. De filtercontext van het subtotaal van Project 1 bestaat uit dus drie regels. In de draaitabel zie je deze getallen in de kolommen waar de metingen staan, [Aantal taken] en [Aantal voltooide taken].

Vergelijk dit met Project 3. Deze bestaat uit vier taken. In de draaitabel zie je dat dit aantal overeenkomt voor [Aantal taken]. Voor [Aantal voltooide taken] zie je daarentegen een lege cel. Dit komt omdat geen van de taken voltooid is.

De laatste meting, [Voltooid_], bepaalt of een taak, of een geheel project, voltooid is. Dit wordt berekend door het aantal taken te vergelijken met het aantal voltooide taken.

In principe is dit een rare oplossing. In Excel had je gewoon de logische EN() formule kunnen gebruiken. Helaas werkt de AND() formule in Power Pivot niet zo flexibel als zijn tegenhanger in Excel. Deze verwacht namelijk twee logische waarden, ingegeven als twee aparte argumenten, en vergelijkt of beide WAAR zijn.

Omdat een project uit meer dat twee taken kan bestaan is deze functie dus niet bruikbaar. Je moet dus een andere manier gebruiken om hetzelfde resultaat te berekenen: door het aantal taken te vergelijken met het aantal voltooide taken.

De status berekenen

Nu de basismetingen klaar staan is het tijd voor het echte werk. Je gaat een formule schrijven die per dag een statusnummer teruggeeft. Hieronder staan de verschillende statussen die we willen bepalen voor onvoltooide taken:

1. Gedaan: dag binnen planning en in het verleden
2. Te doen: dag binnen de planning en in de toekomst
3. Te laat: dag na de planning en in het verleden

Je start met twee hulp metingen om de laatste twee statussen te bepalen:

Beide berekeningen resulteren in een logische waarde en maken gebruik van de speciale EN-operator in Power Pivot, de dubbele ‘&&’. Hiermee kun je toetsen of meerdere vergelijkingen allemaal WAAR zijn.

De status per taak

Met bovenstaande hulp metingen kun je een eenvoudige berekening maken om per dag de status van een bepaalde taak te bepalen:

Deze formule maakt gebruik van SWITCH(), een handige functie die van pas komt wanneer je meerdere ALS-DAN constructies nodig hebt. De functie is overigens ook als Excel formule te gebruiken.

Het eerste argument van SWITCH() heeft een logische expressie – een formule/of waarde die een WAAR/ONWAAR teruggeeft. In dit geval gebruik je simpelweg de TRUE() functie.

De volgende argumenten worden altijd in een waarde-resultaat paar opgegeven. Eerst geef je de waarde op die je wilt vergelijken met de expressie in het eerste argument, en daarna geef je het resultaat dat je de formule wilt laten retourneren als de vergelijking WAAR is.

Dit patroon van waarde-resultaat argumenten kun je net zo vaak herhalen totdat je een laatste waarde wilt opgeven voor de categorie ANDERS – vergelijkbaar met het derde argument van de ALS() formule.

Wanneer je de meting in de draaitabel vervangt met [StatusTaak], dan zie je dat deze de juiste nummering teruggeeft per taak:

Echter, wanneer je een project inklapt, dan gaat het nog niet helemaal goed:

Merk op dat er taken in Project 1 zitten die al over de deadline zijn, maar hierboven niet als een status 3 worden weergegeven.

De status per project

Om de status op zowel project, – als op taak-niveau te bepalen heb je een allerlaatste meting nodig die de maximale waarde van [StatusTaak] berekent. Helaas kun je hiervoor niet de MAX() functie gebruiken. Deze werkt alleen voor kolomverwijzingen en niet voor zelf gedefinieerde metingen:

De X-factor

Gelukkig biedt Power Pivot hier een alternatief voor: de zogenaamde X-functies. Dit is een groep functies wiens functienaam allemaal op X eindigt. Een paar voorbeelden hiervan zijn:

• SUMX
• AVERAGEX
• MINX
• MAXX
• …

De X-functies werken allemaal op dezelfde manier.

1. Het eerste argument is altijd een tabel verwijzing. Dit mag ook een berekende tabel zijn, bijvoorbeeld als resultaat van een FILTER() functie.
2. Het tweede argument is een formule. Hierin kun je als het ware een berekende kolom toevoegen aan de tabel uit het eerste argument, uitsluitend met gebruik van de kolommen die daarin voorhanden zijn.
3. De X-functie berekent voor iedere regel in de tabel in het eerste argument, de uitkomst van de formule in het tweede argument. Vervolgens aggregeert deze alle berekende waarden tot de daadwerkelijke uitkomst van de X-formule.

Het mooie van FILTER() is dat je ook een andere meting als tweede argument mag gebruiken. In dat geval worden alle relaties in het datamodel automatisch doorgerekend. Dit is een zeer krachtige feature van Power Pivot.

Met deze nieuwe kennis kun je de uiteindelijke meting schrijven:

Wanneer je deze meting in de draaitabel plaatst zie je inderdaad dat de waarde op project-niveau de juiste waarden teruggeeft.

Visualisatie

Tijd om de nummering van [Status] te coderen naar de juiste kleuren. Selecteer een willekeurige cel in het waarden bereik van de draaitabel. Ga via Start > Voorwaardelijke Opmaak > Markeringsregels voor cellen en kies ‘Gelijk aan…’ uit het uitklapmenu.

In het dialoogvenster dat verschijnt zet je de waarde op 1 en kies je in het tweede vakje de laatste optie uit de keuzelijst: Aangepaste indeling…

Via tabblad Opvulling pas je de opvulkleur aan naar groen. Op tabblad Rand kies je een witte boven en onderrand, en op tabblad Getal kies je bij Categorie voor ‘Aangepast’, en past bij Type de waarde aan naar drie puntkomma’s (;;;).

Herhaal dit voor de overige statussen: 2 = lichtgroen en 3 = donkerrood.

Het resultaat mag er zijn! In één oogopslag zie je welke projecten over de deadline zijn en waar je je bevindt ten opzichte van de planning.

Ook wanneer je een project inklapt:

Samenvattend

Je hebt de planning tool verder verrijkt met een kleurcodering voor de status waarin een project zich bevindt.

• Je hebt geleerd hoe je gegevensvalidatie toepast zodat gebruikers alleen de juiste waarden mogen invoeren
• Hoe je de gebruiker alleen gevalideerde waarden uit een keuzelijst kunt laten selecteren
• Wat logische waarden zijn en hoe je ze kunt toepassen binnen formules, validatieregels en conditionele opmaak
• Hoe de COUNTOWS(), FILTER(), SWITCH() en MAXX() functies werken in PowerPivot.

In het volgende en laatste deel van deze serie gaan je het dashboard verder opmaken zodat het een presentatiewaardig dashboard wordt, in de plaats van alleen een slicer en een simpele (maar ontzettend gave) draaitabel.