How to get CountDistinct for Subtotal and grandtotal

Question

Hi,
I need to get count distinct for each group member and all group member

Input:
SELECT *FROM
(
SELECT 1 USRID,100 CUSTOMERID,200.03 BALANCE
UNION ALL
SELECT 1,200,300.03
UNION ALL
SELECT 1,300,400.03
UNION ALL
SELECT 1,400,500.03
UNION ALL
SELECT 1,400,500.03
UNION ALL
SELECT 1,500,600.03
UNION ALL
SELECT 2,200,300.03
UNION ALL
SELECT 2,300,400.03
UNION ALL
SELECT 2,700,500.03
UNION ALL
SELECT 2,800,700.03
UNION ALL
SELECT 2,800,900.03
) ET
ORDER BY USRID,CUSTOMERID

Expected output:

If I do the CountDistinct I am getting below but I need to get sum of distinct count of all groups which is 5+4 = 9:

Please help!

Thanks,
Eshwar

Answer 1

Hi @Eshwar ，
Based on the usage of COUNTDISTINCT, I propose my solution here:

=SUM(COUNTDISTINCT(Fields!CUSTOMERID.Value,"USRID"))  

As shown below:


Hope this helps.
Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Eshwar ，
Count distinct is a measure (aggregate function). When the measure is used in the total row, it is not summing the distinct count values about but is calculating the function in the context of all the table data.
When you use =Sum(CountDistinct(Fields!CUSTOMERID.Value)) or "add total", you will calculate the number of all non-coincident data in the [CUSTOMERID] field, So you will get "7" in the total.

Best Regards,
Joy

Answer 3

I got the answer.
using "CountDistinct(Fields!CUSTOMERID.Value + Fields!USRID.Value)" gave me required results
Regards,
Eshwar.