# Power BI Exchange

View Only

## Distinct Count Total is not correct

• #### 1.  Distinct Count Total is not correct

Bronze Contributor
Posted 2 days ago
I have a Matrix showing Product Categories, Total Customers (Distinct Count), and Total Sales. When I manually total the distinct count of customers, I get 3,082, which doesn't match the total at the bottom of 1,971.​

How can I get the total to correctly sum the categories?

------------------------------
Mary Moore
Sales Operations Analyst
6153248429
------------------------------

• #### 2.  RE: Distinct Count Total is not correct

Posted 2 days ago

Hi Mary,

Your total is probably correct :)  But your total is not the sum of the distinct customers.  It is still a distinct count of all the customers that bought something.

Example: You have 546 different customers that bought Accessories.  Suppose that all those 546 customers also bought a product from the Laryngeal Mask Airway.

Then the total different customers for Accessories and Laryngeal Mask Airway would still be 800 because you may not count the same customer twice (distinct count)

I hope this helps.

Regards,

Johan

------------------------------
Johan Vermeire
IT Trainer
Xylos nv
0000000
------------------------------

• #### 3.  RE: Distinct Count Total is not correct

Bronze Contributor
Posted 2 days ago
Hi Johan,

Thanks for your reply. I agree with your explanation, but I'm trying to get the sum so that I can take that total of 3,082 and divide it by 1,971 to get the average number of products bought by customer. I'm thinking this may not be possible in BI.

Mary

------------------------------
Mary Moore
Sales Operations Analyst
Franklin TN
6153248429
------------------------------

• #### 4.  RE: Distinct Count Total is not correct

Posted yesterday

Mandy,

2 remarks:

1) If you want to do it the way that you've mentioned in your reply, don't you have than the same problem on the subtotals of the categories?

For example:  the category Accessories has a total of 546 unique customers, but it can be that the sublevel that is showing 3 products (I guess it are products) with values like: 200, 300 and 400 different customers, but some customers appear for different products within that category.   Don't you want to see then 900 as the total? Or is it only in the grand total of the categories that you want that other approach?

2) I would solve it like this (without taken into account my first remark)

measure = SUMX(   VALUES(DimCategories[Category]) , [Total customers])

But I don't know whether it's a better formula than the one from James.

Good luck

------------------------------
Johan Vermeire
IT Trainer
Xylos nv
0000000
------------------------------

• #### 5.  RE: Distinct Count Total is not correct

Posted 2 days ago
Edited by James Yates 2 days ago

I had a similar issue recently. I solved by using countrows and groupby.

Try something like

``measure = COUNTROWS( GROUPBY(sales, sales[category], sales[customer]) )​``

------------------------------
James Yates
Management Analyst 3
------------------------------