cancel
Showing results for
Did you mean:

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

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

 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!
2 ACCEPTED SOLUTIONS
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.

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

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.

5 REPLIES 5
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]))
)``````

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.

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.

Super User

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!