Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello, I have a very long table such as below with data everyday from the past 5 years on which customer visited our store:
Date | Customer ID (stored as text) |
1/1/2000 | 111 |
1/1/2000 | 222 |
1/1/2000 | 333 |
1/1/2000 | 444 |
1/2/2000 | 222 |
1/2/2000 | 333 |
1/2/2000 | 444 |
1/2/2000 | 555 |
1/3/2000 | 222 |
etc for 5 years
I am trying to create a clusterd column chart showing the number of unique customers that visited our store each day, month (drilled up), quarter (drilled up), and year (drilled up again).
I created a column as below:
Solved! Go to Solution.
But I would like the average of distinct count of customer ID's by day
If you want it across days then you need to SUMMARIZE across days. Then add the DISTINCTCOUNT for each day and finally return the AVERAGEX of that column.
Hi, @alya1
Here is my detailed addition to IBendlin, maybe you can try the DAX expression below
Average Unique Customers =
AVERAGEX(
VALUES('Table'[Date]),
CALCULATE(DISTINCTCOUNT('Table'[Customer ID]))
)
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @alya1
Here is my detailed addition to IBendlin, maybe you can try the DAX expression below
Average Unique Customers =
AVERAGEX(
VALUES('Table'[Date]),
CALCULATE(DISTINCTCOUNT('Table'[Customer ID]))
)
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
But I would like the average of distinct count of customer ID's by day
If you want it across days then you need to SUMMARIZE across days. Then add the DISTINCTCOUNT for each day and finally return the AVERAGEX of that column.
Hi Ibendlin, thank you for your response. Would you mind writing out the dax please? I tried Yongkangs above but am still having the commented issue.
Please provide sample data that fully covers your issue. Needs more months and some years maybe.
Please show the expected outcome based on the sample data you provided.
Ah thank you so much! while writting out the data I realized I made an initial mistake! Your idea works 🙂 Thank you again!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
106 | |
105 | |
90 | |
67 |
User | Count |
---|---|
162 | |
133 | |
132 | |
92 | |
88 |