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

[alya1]

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: 

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!

[lbendlin · Accepted 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.

[v-yohua-msft · Accepted solution]

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.

[lbendlin · Accepted 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.

[alya1]

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.

[lbendlin]

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.

[alya1]

Ah thank you so much! while writting out the data I realized I made an initial mistake! Your idea works 🙂 Thank you again!