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.
Hello,
I have a little problem 🙂
How can I calculate a total monthly occurrence of unique customer numbers?
For example: there is a sales table including invoice date, customer number, invoice number, quantities and price
A customer can buy items several times a day.
I need to calculate a total daily occurence of unique customers and then sum it up for months, years, etc.
For example:
day: 2018.01.01 customer: 00001 SKU: SKU00001 Invoice:000001
day: 2018.01.01 customer: 00001 SKU: SKU00055 Invoice:000001
day: 2018.01.01 customer: 00001 SKU: SKU00155 Invoice:000001
day: 2018.01.01 customer: 01011 SKU: SKU00003 Invoice:000032
day: 2018.01.01 customer: 01055 SKU: SKU00003 Invoice:000032
day: 2018.01.01 customer: 01055 SKU: SKU00003 Invoice:000134
day: 2018.01.01 customer: 01055 SKU: SKU00003 Invoice:000143
day: 2018.01.01 customer: 00001 SKU: SKU00001 Invoice:000001
day: 2018.01.02 customer: 00001 SKU: SKU00055 Invoice:000002
day: 2018.01.02 customer: 00002 SKU: SKU00155 Invoice:000001
day: 2018.01.02 customer: 01003 SKU: SKU00003 Invoice:000032
day: 2018.01.02 customer: 01023 SKU: SKU00003 Invoice:000032
day: 2018.01.02 customer: 01023 SKU: SKU00003 Invoice:000134
...
day: 2018.01.31 customer: 01023 SKU: SKU00003 Invoice:000134
etc.
First:
I should count occurrence of unique customer numbers
day 1 - 1145 unique customers
day 2 - 1123 ...
day 3 - 1560 ...
day 31 - 2130 ...
Next:
total monthly occurrence = day1 + day2 + day3 ... day31
How can I do it in an easy way?
Thanks 🙂
Solved! Go to Solution.
Hi @SchoolBoy
Try this MEASURE
Measure = SUMX ( VALUES ( Table1[Day] ), CALCULATE ( DISTINCTCOUNT ( Table1[Customer] ) ) )
Hi @SchoolBoy
Try this MEASURE
Measure = SUMX ( VALUES ( Table1[Day] ), CALCULATE ( DISTINCTCOUNT ( Table1[Customer] ) ) )
Thank you very much, it works very well! 🙂
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 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |