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(%)




      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)?