DISTINCTCOUNT Total not correct

[Roseventura]

I know this issue has been raised ad nauseam, and I have tried many different approaches including 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.

At me wits end!

 

[ImkeF]

Hi @Roseventura 

How about: SUMX ( VALUES ( DimCalendar[Year-Mo] ), DISTINCTCOUNT(PBI_Disti_NICPoscy[ENDCUS]) ) ?

(Replace name of your calendar-table)

[Roseventura]

That gave me a different answer, but it's still not correct.

The measure I created using your suggestion, is:

CountCust Test = sumx( values(PBI_FSCAPF[YEAR-MO]), distinctcount(PBI_Disti_NICPoscy[ENDCUS] ))

where PBI_FSCAPF is my Calendar table.

 

[amitchandak · Accepted solution]

In summarize try year month

Total POS Customers =
Var DISTINCTCUSTOMERS = summarize(PBI_Disti_NICPoscy,PBI_Disti_NICPoscy[Year-Mon],"DistCust",DISTINCTCOUNT(PBI_Disti_NICPoscy[ENDCUS]) )
RETURN
SUMX(DISTINCTCUSTOMERS,[DistCust])

Or share some sample data.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Roseventura]

Your solution worked.  Here is my 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 your 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!