Hi guys I'm a newbie to forum and to power pivot. Cracking tech and going through a chunky learning curve.
As FD/CFO of a motor dealer group I am re-drafting our management accounts with power pivot and Excel 2016.
I am using plenty of cubevalue formulae to get my data from the cube and all is going pretty well on the prototype.
(Pivot tables not really suitable to get the right presentation)
There are however a lot of kpis (business meaning, not powerpivot) which need to be put into the spreadsheet.
My fact table (the one with the ledger amounts) has all monetary amounts and stats in same column distinguished by 2 measures summing Money and Stats. I have then created a measure Divide([Money],[Stats]) as a generic measure to work out per units of various elements of the profit. This measure is then reworked into multiple measures to manage time (YTD,LYTD) and variances.
Then in spreadsheet I have derived cubesets to shrink the data down to that which is necessary to make the measure work properly. All is working fine, albeit the getting data refresh is taking a little time.
An alternative way to do what I'm attempting here would be to create multiple measures Divide(Calculate([Money],LedgerCategories.[Cat01] = "Model Gross"),[Stats), Divide(Calculate([Money],LedgerCategories.[Cat01] = "Accessory Gross"),[Stats]) etc and just reference each measure separately in the cube formula, rather than measure and cubeset.
So technical question is which option would give me a faster, more efficient spreadsheet:
1. A simple measure with a series of Excel derived cubesets. (MDX code in a cell, referenced by a cubeset formula).
2. Multiple measures in the model, referenced with simpler Cubevalue formulae.
Whichever way I go here I either have a lot of cubset MDX to derive or a lot of measures to derive, so if I know in advance which is the optimum approach that would be really helpful.
Looking forward to your advise.
Pete
