Announcements
cancel
Showing results for
Did you mean:
Creator III

## Percentages exceeding 100% when dividing by Count Total Distinct

Hello, I have a data set as follows:

Month

MonthVendorDistinct ID Count
JanA7451
B6260
C563
FebA7260
B6250
C560
MarA7128
B6266
C540
AprA7138
B6285
C559

Now I want to display the percentages of the distinct IDs per vendor for each month. For example: In January, Vendor A percentage is

7451/(7451+6260+563)*100=52.199%. I'm using the following expression:

Count(distinct(ID))/Count(distinct total <[Month]> (ID))*100

But this expression is giving me the percentages as 62,52,5 sum of which is exceeding 100%. Can you please help me get my expression right? TIA!

Labels (1)
• ### function

1 Solution

Accepted Solutions
MVP

Count(DISTINCT ID)/Sum(TOTAL <Month> Aggr(Count(DISTINCT ID), Month, Vendor)) * 100

6 Replies
Master II

Distinct ID Count is the count(ID) ?

use aggr function with sum to get the total of count

something similar to

Count(distinct(ID)) -> 7451

aggr(Count(distinct total <[Month]> (ID)),Month) to get the temp data then sum it.

Learning never stops.
Creator III
Author

Hello. Thanks for the reply. So I used this expression:

Count(distinct(ID))/Sum(aggr(Count(distinct total <[Month]> (ID)),Month)). But it's still not giving me the correct output. Infact I'm getting everything as zeroes now. Can you point out if I'm doing anything wrong?TIA!

MVP

Count(DISTINCT ID)/Sum(TOTAL <Month> Aggr(Count(DISTINCT ID), Month, Vendor)) * 100

Creator III
Author

As always you are spot on! Can you give me a brief explanation of how the expression in the denominator works if you don't mind? Thanks a lot!

MVP

Without the Aggr function, the DISTINCT ID was giving you the total DISTINCT ID for the Month (in other words if an id repeated within a single month for multiple vendors, it was just used once for the denominator). When we introduced Aggr(), it found the distinct ID per Month per vendor and then summed it

Creator III
Author

That makes sense. Thanks a ton!