Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
alya1
Helper IV
Helper IV

Easy way to show average by month/quarter/year for date hierarchy of distinct count of text?

Hello, I have a very long table such as below with data everyday from the past 5 years on which customer visited our store:

 

DateCustomer 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: 

Total unique customers = DISTINCTCOUNT('Table'[Customer ID])
This works fine when the bar chart is on date level but not at month/quater/year (I think it is summing all distinct counts for that time span. But I would like the average of distinct count of customer ID's by day (per month/quater/year) please.

For example, if 15 days out of 30 we got 5 unique customer IDs and the other 15 days out of 30 we got 10 unique cusotmers IDs then the average of that month should show 7.5 unique customers IDs (doesn't matter their actual ID). 

Thank you!
2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

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.

View solution in original post

v-yohua-msft
Community Support
Community Support

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]))
)

vyohuamsft_0-1709015446391.png

 

 

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.

 

View solution in original post

5 REPLIES 5
v-yohua-msft
Community Support
Community Support

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]))
)

vyohuamsft_0-1709015446391.png

 

 

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.

 

lbendlin
Super User
Super User

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!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.