Forum Discussion
DISTINCTCOUNT Total not correct
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!
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
4 Replies
Hi Roseventura
How about: SUMX ( VALUES ( DimCalendar[Year-Mo] ), DISTINCTCOUNT(PBI_Disti_NICPoscy[ENDCUS]) ) ?
(Replace name of your calendar-table)
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.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
