5 Replies Latest reply: Feb 4, 2014 2:21 AM by Jan-Hendrik Esterhuyse RSS

    Percentage % Total for Dimension in Pivot Table

    Jan-Hendrik Esterhuyse

      Hey

       

      Hope someone can assist me with the following

       

      I have a pivot table with 2 expressions over three dimensions

       

      The dimensions are:

      Phase, Type and Side

       

      The expressions are:

       

      For Nr:

      count(rowno)

       

      For %:

      count(rowno) / count(total rowno)

       

      (See next image)

       

       

      In the pivot table, I would like to show the % as summing up to 100%.r1.png

       

      For example, in the image, in the column "CUT" under %, it should show 100% in the top total, and then for example General Play - Off-Side should show 20% (see image below where I have just filtered down to side). So, where it shows 2.41% in the top image, it should show 20%

       

       

      r2.png

      I have tried several different combinations of aggr functions, for example

       

      sum(aggr( count(rowno) / count(total rowno),Side))

       

      or count(rowno) / sum(aggr(count (total rowno),Side))

       

      and a few others, but have not been able to resolve.

       

      Do anyone have an idea which could help?

       

      I'm attaching the model with the object (data reduced, so % can differ from mentioned above), hope this helps