Percentages exceeding 100% when dividing by Count Total Distinct

[mrthomasshelby]

Hello, I have a data set as follows:

Month

Month	Vendor	Distinct ID Count
Jan	A	7451
	B	6260
	C	563
Feb	A	7260
	B	6250
	C	560
Mar	A	7128
	B	6266
	C	540
Apr	A	7138
	B	6285
	C	559

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!

stalwar1‌

[pradosh_thakur]

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.

[mrthomasshelby]

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!

[sunny_talwar · Accepted solution]

How about this

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

[mrthomasshelby]

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!

[sunny_talwar]

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

[mrthomasshelby]

That makes sense. Thanks a ton!