Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Sebastian
Advocate II
Advocate II

Wrong Total in Matrix while using Distinctcount

Hi all,

 

is there a problem with the Matrix visual if u use distinctcount? The subtotals are all right but the total at the end is wrong.

Did someone have an idea?

 

 

Thanks.

1 ACCEPTED SOLUTION

That is what I thought...

 

If a customer bought something in february and march, it will only be counted once for february, once for march, and once for the total. This is what DISTINCTCOUNT does.

 

In your case, you may want to use a SUMX function in your measure to force the results you want.

 

Edit: How should the measure behave when you show your data on a daily basis? Again, if a customer bought something on the first and the second of january, how many times should it be counted for the month of january (subtotals).

 

 

 

 

View solution in original post

6 REPLIES 6
LaurentCouartou
Solution Supplier
Solution Supplier

What do you mean with "wrong"? Can you provide an example?

 

If you expect totals to be the sum of subtotals, just remember DISTINCTCOUNT is not additive.

 

I would like to count all different Customer of each month which bought something

 

For example

 

Month    Distinct Count

------------------------

January   15

Feb         10

March     12

-------------------------

Total       37

 

But the Matrix shows as total 30 for example. And this is wrong.

Hello Sebastian,

 

You have to create one caluclated coloumn that gives you concatenation of both "Month" and "Customer" then you can take distict count of the new caluclated coloumn.

 

Clauclated cloumn = 'Table'[Month] & 'Table'[Customer].

 

Please let me know you need more information.

That is what I thought...

 

If a customer bought something in february and march, it will only be counted once for february, once for march, and once for the total. This is what DISTINCTCOUNT does.

 

In your case, you may want to use a SUMX function in your measure to force the results you want.

 

Edit: How should the measure behave when you show your data on a daily basis? Again, if a customer bought something on the first and the second of january, how many times should it be counted for the month of january (subtotals).

 

 

 

 

ah ok. Haven't understand what you have meaned before. Sorry for that.

Thanks for ur answer. I'll try it with sumx.

 

Thank you.

Hi All,

 

Couled please share me the dax please.

 

I got same problem.

 

Thanks,

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.