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 table with following schema:
Date Customer ID
13.1.2020 A
13.1.2020 A
13.1.2020 B
20.1.2020 C
1.2.2020 B
14.2.2020 A
I need to count the distinct Customer-ID per day which serves as the number of orders, so the result would be
A= 2
B= 2
C=1
This column "number of orders" should sum up over a filtered time. I tried it with this formula, but the sum of the counted rows per month doesn't show the right total:
Any recommendations how to solve this? Thank you!
Hi @clarissa ,
Create a measure
Hi Harsh
Thank you for your feedback. Unfortunately the number shows way too less orders when I calculate it this way... I only get 12 orders per month April although it should be much more.... Could it be that this formula only counts distinct customer_ID per filtered time frame? So if a customer orders 4x in April and I filter April, that I only get 1x the customer?
I would need every customer ID per day as 1 count and if that customer ID appears 2x or 3x it should count it only 1x per day..
Was it clear what I try to achieve?
Thanks again for your help and best regards, Clarissa
When you tried the expression by @harshnathani did you visual have Customer ID in it? In any case, if you modify that expression as follows, it should work whether it is or not.
Distinct Count of Customer Day = COUNTROWS(SUMMARIZE('Table','Table'[Date], 'Table'[customer_ID]))
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
thank you Pat.
Can you explain me, what this Summary function with the 2 groups (date, ID) actually does?
and why does this formula not work?
Hi,
Try SUMX(disctinctcount ...
You need an interator to sum subtotals.
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi Steve,
thank you. I tried it with your proposal:
Best regards
Clarissa
Perhaps something more explicit:
Sumx(values(table[date], distinctcount..
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi Steve
so you would write it like this?
Yes, if this doesn't work we need to know the data model.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi Steve
Unfortunately it doesnt work.
The data model:
- 1 table factinvoice with revenues and invoice dates (*)
- 1 table with customer (1)
- join through customer_id: 1 to *
Does that help? Thanks for your support!
You'll want to have different calculations depending if you are in a "row" or in the total. Use HASONEVALUE() to differentiate.
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 |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |