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?

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hello Aimee,

    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.
    Kind Regards,

    Reply Like
  • Yakov Shaul Not entirely... I want to see the distribution of the inner most hierarchy such that for the example you have the distribution of bikes by year is shown, for example, All Years would be 100%, 2008 would be a percentage of that total and so on.  Does that make sense?

    Reply Like
  • 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.


    Reply Like 2
  • Thank you, this is what I was looking for

    Reply Like
reply to topic
Like Follow
  • Status Answered
  • 1 mth agoLast active
  • 4Replies
  • 248Views
  • 3 Following