Forum Discussion
Make Customer Count Column using Distinct Values
Hi Everyone,
I am having a bit of trouble creating a customer count column based on the below information:
Essentially, I would like a column where customer count is equal to 1 if the document number (last column) has a distinct number on a specific "report date" and "sold date". Please may someone assist.
Kind regards,
Dylan
Hi dylanndengu ,
You can use the following measure :
Measure = SUMX(SUMMARIZE('Table','Table'[report date],'Table'[sold date],"countofcustomer",DISTINCTCOUNT('Table'[document])),[countofcustomer])Sample data:
Result:
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
6 Replies
dylanndengu , nor very clear , Try measure like
Distinctcount(Table[documentnbr])
or
Countx(summarize(Table, Table[reportdate], table[soldate], "_1",Distinctcount(Table[documentnbr])),[_1])
Hi Amit,
Thank you for responding but I do not thing the solution will work, and it is probably due to my lack of clarity.
The data I have takes into account everything a customer has bought on a specific day and assigns that transaction a number (documnetnr). The issue is if one customer buys a soda in addition to a bucket of chicken, those are recorded as 2 different transactions with the same document number. Hence, in order to calculate the actual number of customers, I need to only count distinct document numbers on a specific day, as the document numbers generated restart every 24 hours. So you can't simply take a distinct count of the document numbers because new ones are generated every day. I'm thinking I could perhaps group by report date, sold date and document number and then do a distinct count on that? Please let me know what you think. I hope that clarifies things a bit.
dylanndengu Not really cleare. Based on my understanding you may create the below measure:
Measure = CALCULATE(DistinctCount(TableName[DocumentNumber]),ALLEXCEPT(TableName,TableName[ReportDate]))
Apologies for the lack of clarity. The data I have takes into account everything a customer has bought on a specific day and assigns that transaction a number (documnetnr). The issue is if one customer buys a soda in addition to a bucket of chicken, those are recorded as 2 different transactions with the same document number. Hence, in order to calculate the actual number of customers, I need to only count distinct document numbers on a specific day, as the document numbers generated restart every 24 hours. I hope that clarifies things a bit.
Hi dylanndengu ,
You can use the following measure :
Measure = SUMX(SUMMARIZE('Table','Table'[report date],'Table'[sold date],"countofcustomer",DISTINCTCOUNT('Table'[document])),[countofcustomer])Sample data:
Result:
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
