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.
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.
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.
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.
