Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
@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
This is great, thank you so much
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |