Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)?
You can try an advanced aggregation, like
=sum( aggr( count(DISTINCT ...),Dimension))
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?
Should work, just add all your table dimensions to the aggr() function's dimension list:
=sum( aggr( count(DISTINCT ...),Dimension1, Dimension2, Dimension3))
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%.
Try
sum(
aggr(
count(distinct Person) / sum(total aggr( count(distinct Person),Dimension1,Dimension2,Dimension3))
,Dimension1,Dimension2,Dimension3)
)
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!
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.