Combineren van meerdere databronnen in PowerPivot en de enorme efficiencyslag in tijd.

Afgelopen week werd me gevraagd mee te denken over een oplossing om naast commerciële stuurinformatie vanuit een CRM pakket ook financiële gegevens vanuit Exact te betrekken in één rapportage vanuit meerdere entiteiten.

Kortom, een leuke uitdaging om eens verschillende SQL databases met elkaar te koppelen via PowerPivot, gebruikmakend van PowerQuery, om de juiste informatie te masseren en samen te voegen.

Ik wist dat deze rapportages tot op heden handmatig middels plakken en knippen, filteren etc vanuit diverse bronnen werd samengevoegd. De rapportages hadden een frequentie van 1x per kwartaal en waren erg arbeidsintensief te noemen. Gemiddeld 2 weken per jaar werd eraan gewerkt.

De uiteindelijke format werd aan mij voorgelegd met de uitdrukkelijke wens om de stuurinformatie sneller en op maandelijkse frequentie te rapporteren.  Natuurlijk kost het tijd uit te zoeken waar de informatie in de verschillende tabellen staat opgeslagen en volgende deze juist te koppelen zodat er nuttige informatie ontstond.   Het koste me uiteindelijk 2x een halve dag om te ontwikkelen.

Ik kan nu met behulp van één slicer iedere klant of klantengroep selecteren voor een bepaalde periode en terugblikken op oude periodes en natuurlijk een grafische weergave van het verloop presenteren. Een report per klant opstellen kost me nu 10 seconden. 

Hieronder de uiteindelijke structuur in PowerPivot  met daarin :
1. CRM/verloningstabellen uit Flexservice SQL2005
2. gefilterde en gemasseerde tabellen uit Exact SQL2008
3. Hulptabel uit Excel

SUMX & IF om een outlook van een jaar te berekenen

Een tijdje geleden kwam bij mij de vraag om een outlook te berekenen van actuals en budget data.
Beide tabellen hadden periodieke data en ik besloot om van de gerealiseerde periodes de actuals te combineren met de budgetcijfers van de niet gerealiseerde periodes. Dat deed ik met behulp van de measure outlook met de functie: IF actual = 0; budget; actual.

Een prima werkte measure totdat ik de tabel liet totaliseren. Wat Powerpivot doet is simpelweg bij de totaaltelling de totalen van de actual en budget vergelijken en (aangezien de som van de actuals niet 0 is) het saldo van actuals als totaal opgeven voor de tabel outlook.

Hoe kon ik nou de juiste outlook berekenen?

Hieronder in een voorbeeld de problemen en oplossing uitgewerkt:

figuur 1a: 3 eenvoudige tabellen , actuals, budget & periode

figuur 1b: als volgt gelinkt in Powerpivot

figuur 2: ik voeg de tabel actual en budget toe aan mijn report en heb een measure  gecreëerd voor de outlook. Ik noem hem  Outlook fout =if(SUM(Actual[actual])=0;sum(Budget[budget]);sum(Actual[actual]))
Je ziet hier dat periodiek juist wordt gekozen tussen actuals en budget, maar dat het eindtotaal van €70.000 niet klopt.Dat had € 255.000 moeten zijn.

 figuur3: de measure Outlook fout.

Ik heb op internet gezocht naar oplossingen, maar vond niet direct een vergelijkbaar probleem, wel een aantal aanwijzingen dat de oplossing ergens lag in een combinatie van SUMX en IF.

Na enige tijd kwam ik op de volgende measure voor een outlook:
Outlook beter = SUMX(VALUES(per[per]);IF(sum(Actual[actual])=BLANK();sum(Budget[budget]);sum(Actual[actual])))

Ik had ergens gelezen dat een SUMX icm IF, mits je de periodiek totaliseert, juist zou optellen.
Helaas klopte de totaaltelling van € 840.000   (12 x  het totaal van de actuals a € 70.000) niet met de uitkomst die ik zocht.
figuur 4

Ik besloot om twee extra measures te maken TotalActual en TotalBudget
TotalActual = sum(Actual[actual])  & TotalBudget =sum(Budget[budget]) en daarna verving ik in de outlook measure sum(Actual[actual]) voor TotalActual  & sum(Budget[budget]) voor TotalBudget het werkt!
Kennelijk moet je eerst een totaal berekenen en de uitkomst daarvan in een tweede meting te stoppen.

De measure voor de outlook wordt dan :
Outlook goed=SUMX(VALUES(per[per]);IF([TotalActual]=BLANK();[TotalBudget];[TotalActual]))

Figuur5 de oplossing met juiste outlook.