Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to 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
Hi @Roseventura
How about: SUMX ( VALUES ( DimCalendar[Year-Mo] ), DISTINCTCOUNT(PBI_Disti_NICPoscy[ENDCUS]) ) ?
(Replace name of your calendar-table)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
That gave me a different answer, but it's still not correct.
The measure I created using your suggestion, is:
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
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!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
86 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
112 | |
99 | |
69 | |
67 |