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

[#1]

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:

Dimension	Distinct Count	Ratio(%)
Group1	6	60%
Group2	4	40%
Group3	2	20%
TOTAL	10	100%

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

[swuehl]

You can try an advanced aggregation, like

=sum( aggr( count(DISTINCT ...),Dimension))

[#3]

Thanks. I considered using aggr but didn't follow up on it since there is more than 1 dimension. Could I use the same method for 2 or 3 dimensions?

[swuehl]

Should work, just add all your table dimensions to the aggr() function's dimension list:

=sum( aggr( count(DISTINCT ...),Dimension1, Dimension2, Dimension3))

[#5]

Thanks. When I do a total sum it does kind of work.

The sum of all individual rows do add up to 100%. But now the total percentage is less than 100% and the sum of the subtotal are also less than 100%. The individuals rows do not add up to the subtotals and the subtotals do not add up to the total or to 100%.

[swuehl]

Try

sum(

     aggr(

          count(distinct Person) / sum(total aggr( count(distinct Person),Dimension1,Dimension2,Dimension3))

     ,Dimension1,Dimension2,Dimension3)

)

[#7]

That doesn't help either. And it's not possible to fit it along with other functionalities I have in the pivot table.

So I decided to change what I am counting to avoid this problem. Thanks for your help!    

[#8]

Hi,

Im facing the same problem. I am trying to create a bar graph with the percentage of total number of people who have A's. so i am using the formula:

Count

({$<NewScore={'A'}>} DISTINCT UserName)/Count(DISTINCT UserName)

But the answer is showing more than 100%. Can u please help.