Hi all,
I currently have a P&L that requires a mix of values in dollars and percentage. For example - standard P&L, in the last row a contribution margin as %. The users often change the report to show different formatting as well for example for the $ it may be thousands, millions etc.
To overcome this, I've created a calculation group which effectively says:
IF (
SELECTEDVALUE ( 'Report Hierarchy'[Reporting line]) = "Contribution Margin",
"#,0%",
"#,0,;(#,0,);-"
)
This is repeated for a range of options (Thousands, Thousands 1 decimal, Thousands 2 decimals, Millions, Millions 1 decimal).
There are a lot of figures on the page {~ 7 departments (columns), ~20 reporting lines (rows), 5 measures (sub-columns) } so I'm conscious this is executing over 700 individual values in the table.
When using the calculation group, it adds 130% to the calculation time. Currently running at 2,027ms, adding the formatting pushes it to 4,841ms. I recognise this is all slow but I'm breaking down each part contributing to the performance to reduce the overall time.
I've reproduced a simplified version which I've dropped here. In this example it takes 162ms to load but when the formatting is applied the timing increases to 250ms (54% increase).
2 part question really:
(1) Is this an efficient way to apply a dynamic value format?
(2) Why is the time more than duplicated in executing the code? Assuming all it is doing is checking the selectedvalue in the report line and applying one of two formatting options, wouldn't the performance of the underlying code be irrelevant - or is this method resulting in the original measure being executed twice?
Thanks in advance.
