We have to display 70 KPIsin a single sheet and each of them is (almost always) split across 4 columns (LYTD, YTD, Target, Target vs YTD), i.e. there roughly are 280 actual KPIs ! To be more explicit, the expected result is a table made of 70 rows and 6 columns (2 dimensions - KPI group and KPI - and 4 expressions). This means that, each table cell has to evaluate a different formula based on its row and column.
We managed to organize all the formulas in a single Excel file whose structure is very close to the one of the expected pivot table and where each KPI is identified by a code (RTA measure index). I have attached a revised copy of this file.
After many trials and errors, I have reached the result by using for each expression a very similar formula based on pick and fieldvalue (instead of if and fieldvalue), but the performance is considered poor... (30 s on a 2.4 GHz dual core, 12 GB, dedicated machine).
pick( [RTA_FORMULE.RTA measure index],
num($(=fieldvalue('RTA_FORMULE.RTA measure formula 2',1)),'$(vRTAMeasureAAA_F_2)'),
num($(=fieldvalue('RTA_FORMULE.RTA measure formula 2',2)),'$(vRTAMeasureAAB_F_2)'),
num($(=fieldvalue('RTA_FORMULE.RTA measure formula 2',3)),'$(vRTAMeasureAAC_F_2)'),
num($(=fieldvalue('RTA_FORMULE.RTA measure formula 2',4)),'$(vRTAMeasureAAD_F_2)'),
...
num($(=fieldvalue('RTA_FORMULE.RTA measure formula 2',70)),'$(vRTAMeasureACR_F_2)'),
)
What could be used in order to speed up the performance in this scenario?
I have actually tried a few things such as using dynamically created variables to host each of the 280 formulas and I was actually able to place in each cell the right variable name but I haven't been able to proceed with the next step, i.e. interpreting variables' content as a formula and evaluate it to get the right numbers. Enable the last expression to see what I mean.
Please have a look to the attached qvw. Sorry for its size, but the original file could not be easily filtered and then scrambling made it even bigger..
Just a quick additional question: provided that we find the way to efficiently select the right formula for the right cell, the table still has to perform a different calculation for each of these cells; does this mean that performance can't really be optimized?
