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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
alya1
Helper II
Helper II

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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