Conditional formatting: percent of total
This has always been one of my favorite parts of BI Office, however I'm struggling when I have more than one dimension. Here's my report set up: dimensions P & Q on columns, dimension A and B on rows, measure in the subsets. I want to show the percent of the "all member" each member of dimension B represents for each column A member. I have tried every combination of the conditional formatting options that I can think of and it does not show the way I want it to. Do I have other options beside creating a custom calculation on dimension B to show my percent of total?
I used this analysis DB Pyramid Demo 2013 abf
Selected the "Pyramid Sales Demo" cube.
And created the attached report (Pyramid Book)
Look at the attached image and let us know if this was your expected result.
I think what you re looking for is a combination of the hierarchy and inner hierarchy options in the conditional formatting logic, i.e. apply the % calc to each set of inner dimension members hierarchically within each outer dimension member. Unfortunately, from what I can determine, this is not possible using conditional formatting and you will have to create a calculated measure to do this.
However, this is a pretty straightforward thing to do using the calculation designer.
I'm using the DIVIDE function here as it will handle the issue of no parent of the ALL level when performing the calculation, which would otherwise show #.INF% in the grid. The DIVIDE function expects 3 arguments. The numerator, in this case set to Sales measure and the current member of the inner dimension on the rows, in this case, Month Dates (above). The divisor, again set to measure of sales but the Month Dates set to Parent (below):
The third (optional) argument sets the result to 1 if there is any error. I.e., if there is no parent value (such as the case where the current member of the date dimension is All Dates), then set the value to 1 rather than infinity. Alternatively, you could set it to null so that percentages are not shown for the All Dates rows.
This results in a grid like this:
which I think is what you are after.
Hope that helps.