DISTINCTCOUNT Total not correct

I know this issue has been raised ad nauseam, and I have tried many different approaches and watched many of your videos on the subject. Some of the measures I’ve tried include ISFILTERED, HASONEFILTER, HASONEVALUE, and SUMMARIZE. I still cannot get my table to give me a total of Distinct Customers.

The TABLE[Field] I’m using to count the customer names is the PBI_Disti_NICPoscy[ENDCUS] . I need my visual table (below) to show me how many distinct customers had orders for each month and then of course the grand total at the bottom. My problem is that the grand total is incorrect.

Here is the measure I am currently using:

Total POS Customers =
Var DISTINCTCUSTOMERS = summarize(PBI_Disti_NICPoscy,PBI_Disti_NICPoscy[ENDCUS],“DistCust”,DISTINCTCOUNT(PBI_Disti_NICPoscy[ENDCUS]) )
RETURN
SUMX(DISTINCTCUSTOMERS,[DistCust])

The total at the bottom represent ALL the distinct customers, which in my summary table is 8,771. I’ve tried so many different things, but I still keep getting 8771. The correct total should be 12,660.

Capture

Hi Rose,

Can you please share your model diagram?

Hi Rose,

Do you need to setup a summary table for your model to work?

Customer Count = DISTINCTCOUNT(‘Table’[ENDCUS])

Once that’s in place, your visual can use the new measure and your numbers should lineup correctly.

I hope this helps,

mickeydjw

Hi Rose,

I simulated your scenario and it works. Please use below measure:

Distinct customers =

VAR summarytable = ADDCOLUMNS(‘Date’,“distinct”,CALCULATE(DISTINCTCOUNT(‘Customers’[Customer ID])))
RETURN
IF(ISFILTERED(‘Date’[Month]),DISTINCTCOUNT(‘Customers’[Customer ID]),SUMX(summarytable,[distinct]))

Result:

image

Data:
image

Please mark solved if it solves your problem.

Hi Rose, we’ve noticed that no response has been received from you since the 11th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

It seems that a summary table is the best approach to solve this problem. But I don’t necessarily need a summary table if there was another way to solve this.

I tried your Customer Count measure and all that does is give me a 1 in every column. So I’m not sure what that’s supposed to do or what to do with it.

Capture31

Hi Hafizsultan,

I tried your suggestion and it’s very similar to the one I posted in my original post. In any event it did not work. It gave me very high numbers. I’m not even sure where they are coming from. At least the total is correct. I just don’t know where these other numbers are coming from. The numbers in the first column are the correct customer counts for these months.

Here’s the measure:

CountCust Test = VAR summarytable = ADDCOLUMNS(PBI_FSCAPF,“Distinct”,CALCULATE(DISTINCTCOUNT(PBI_Disti_NICPoscy[ENDCUS])))
RETURN
IF(ISFILTERED(PBI_FSCAPF[YEAR-MO]),DISTINCTCOUNT(PBI_Disti_NICPoscy[ENDCUS]),SUMX(summarytable,[Distinct]))

PBI_FSCAPF is my Date table.

Also, I don’t know if this matters, but I’m not trying to count VALUES. I’m trying to count the Customer name field which is an alpha field. I don’t have a customer ID or number.

Capture32

Disti Inventory Profile.pbix (2.3 MB)

I was able to solve this problem. Here is the ORIGINAL measure which did not work:

Total POS Customers =
Var DISTINCTCUSTOMERS = summarize(PBI_Disti_NICPoscy,PBI_Disti_NICPoscy[ENDCUS],“DistCust”,DISTINCTCOUNT(PBI_Disti_NICPoscy[ENDCUS]) )
RETURN
SUMX(DISTINCTCUSTOMERS,[DistCust])

and here is the amended measure which did work:

Total POS Custs =
Var DISTINCTCUSTOMERS = summarize(PBI_Disti_NICPoscy,PBI_Disti_NICPoscy[Year-Mo],“DistCust”,DISTINCTCOUNT(PBI_Disti_NICPoscy[ENDCUS]) )
RETURN
SUMX(DISTINCTCUSTOMERS,[DistCust])

The difference was the second argument in the SUMMARIZE table. I was using the ENDCUS field and I should have been using the YEAR-MO field.

You can mark this solution as resolved.

Thank you everyone!

Capture33

1 Like

That’s great Rose that you solved your issue. It becomes difficult to simulate exact issue without pbix model. Kindly attach pbix file if possible in future if you need any quick solution as all members of this forum are very active.

You can mark it solved by clicking on solution icon like below. I believe it is helpful to keep track of open cases.

image