topic Re: Percentages exceeding 100% when dividing by Count Total Distinct in App Development

Percentages exceeding 100% when dividing by Count Total Distinct

mrthomasshelby — Wed, 22 Dec 2021 15:42:54 GMT

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‌

Re: Percentages exceeding 100% when dividing by Count Total Distinct

pradosh_thakur — Thu, 21 Sep 2017 10:39:15 GMT

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.

Re: Percentages exceeding 100% when dividing by Count Total Distinct

mrthomasshelby — Thu, 21 Sep 2017 11:03:11 GMT

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!

Re: Percentages exceeding 100% when dividing by Count Total Distinct

sunny_talwar — Thu, 21 Sep 2017 11:09:46 GMT

How about this

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

Re: Percentages exceeding 100% when dividing by Count Total Distinct

mrthomasshelby — Thu, 21 Sep 2017 11:12:50 GMT

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!

Re: Percentages exceeding 100% when dividing by Count Total Distinct

sunny_talwar — Thu, 21 Sep 2017 11:14:56 GMT

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

Re: Percentages exceeding 100% when dividing by Count Total Distinct

mrthomasshelby — Thu, 21 Sep 2017 11:24:16 GMT

That makes sense. Thanks a ton!