This is close, but not quite what I'm looking for. It appears the percentage delta you have calculated is the delta between the cell percentage and the average percentage for each column group. I want the delta to be between the cell percentage (i.e. 37.25% for the <=17, COD # 105 cell) and the grand total column percentage for that row (which is 20.08% for COD # 105). So for that cell, I want the delta to be 17.17%.
Hopefully this explains what I want:
Thanks for helping!
This is exactly what I wanted. However, I'm encountering an issue with the calculations in my primary workbook. In it, I'm using a parameter rather than the COD variable, which I don't think poses an issue, but something somewhere is causing an issue with the calculation.
One would expect the percent delta to be 0 for all the grand total values, but that is not the case. In some cases where I would expect a large delta (like 18%), I'm getting a small delta (like 2%), like in the picture above.
Because the calculation contains LOD fixed,. if your worksheet contains any filters, you need to change the filters to context.
Create Context Filters
To create a context filter, select Add to Context from the context menu of an existing categorical filter. The context is computed once to generate the view. All other filters are then computed relative to the context. Context filters:
- Appear at the top of the Filters shelf.
- Are identified by a gray color on the Filters shelf.
- Cannot be rearranged on the shelf.
- Removing the field from the Filters shelf – If other context filters remain on the shelf, a new context is computed.
- Editing the filter – A new context is computed each time you edit a context filter.
- Selecting Remove from Context – The filter remains on the shelf as a standard filter. If other context filters remain on the shelf, a new context is computed.
Do I need to redo the calculations or anything after adding the filters to context? Even if I add all the filters to context, I get the same results as before, with the grand total deltas not being 100%.
Edit: Not sure if this helps, but for each of the rows, the delta results are summing to 0 in most cases, but not all. So if I set the parameter to one with two values, the delta for value one may be -0.023 and the delta for value two will be 0.023.
Does anyone else happen to have any input on this? Changing the filters to context did nothing to change the final results that I was previously getting. The deltas for the grand total columns are still not 0% and the deltas for each of the cells is still incorrect despite following Shin's calculations above. The calculations work in the provided example workbook, but not the primary workbook I am working on.
Calculation for column percentage seems to be working as expected:
Calculation for grand total percentage(?) does not appear to be working as desired:
Calculation for delta is not working as desired, I assume because the grand total percentage calculation isn't working as expected:
Any help is appreciated!