7 Replies Latest reply: Aug 1, 2013 4:34 PM by Vidya Ramanujam RSS

    How to get percentages to add up to 100% when dividing by the distinct count

      I have an issue with a pivot table where I am counting distinct people and also have a percentage of each category dimension to the total sum.

      Due to the distinct count the percentages always add up to over 100%

       

      Here is a simplified example:

       

      DimensionDistinct CountRatio(%)

      Group1

      660%
      Group2440%
      Group3220%
      TOTAL10100%

       

      The total of ratios will be 120% if added up individually. I understand why this happens and I am wondering if there is a way to work around this.

      In the Ratio expression I divide by the distinct total count which causes this.

       

      Dividing by the non-distinct total is not an option since then in each group I would count more people than there actually are. Since there are much nore non distinct records in total then the total percentage would be much less than %100.


       

      So my question is: Is there a dynamic way to divide by the sum of the rows(12 in this case)?