Hi everyone,
I'm trying to build a report with using many measures and parameters across categories like so:
Lets say I have 5 categories. I have a set of global WhatIf paramters that get applied to calculations for all 5 categories, and a set of WhatIf parameters specific to each category. So in total I have (# global parameters + #categorical parameters*5) WhatIf parameters.
Ultimately, the end result I'm looking for is a table of percentile calculations for a list of percentiles for all 5 catgories aggregated, as well as each category individually.
My data consists of 50k rows with unique RowIds. Because I'm using WhatIf parameters, everything has to be done via measures. First a single category, I first set the global parameters and the categorical parameters. I then use:
PERCENTILEX.INC(Table, Table['Column]*GlobalParam1 + CatagoricalParam1*CategoricalParam2, 1-MAX(percentile))
In this case, "percentile" is a list of percentiles of interest. I then use a matrix to view the percentile list and the result of this calculation for that percentile. This is fine and this works, however, I have to do another 5 calculations for each catagory, across all categories, using many parameters, so the number of calculations and measures I need to use add up very quickly and cause any change in parameters to take a very long time to update.
Is there a better approach to this? It would be much easier if I could do things on a column basis directly in the table, but since I'm using WhatIf parameters, it looks like I can only use measures.
One other quick question I had is how to fix a measure calculation. For example, I use a percentilex function with a whatif parameter on a table to calculate a limit. I then want to use this limit in further measure calculations on the same table. However, since it is a measure from the same table, each row recalculates a different limit. I simply want to store the limit across the whole table as a fixed value. Is there a way to do this?
Thank you and please let me know if I can clarify anything!
