DAX - Grand Average of Count

[JB2020]

I am looking to create a measure to show a grand average of the number of occurrences of each date in the Data table, to display in a chart. I have provided an example below, please let me know if you need more information though. Any help is appreciated.

Calendar Table

Date

01/01/2020​

02/01/2020​

03/01/2020​

Data Table

Dates

01/01/2020​

01/01/2020​

02/01/2020​

02/01/2020​

02/01/2020​

03/01/2020​

03/01/2020​

03/01/2020​

03/01/2020​

Desired result

Date	Count	Grand Average
01/01/2020​	2​	3​
02/01/2020​	3​	3​
03/01/2020​	4​	3​

 

[RoryA]

There must be a better way, but this seems to work:

=CALCULATE(AVERAGEX(Data,COUNTX(VALUES('Calendar'[Date]),[Dates])),ALL('Calendar'[Date]))

 

[RoryA]

I don't know if this is more efficient or not, but you could also use:

=AVERAGEX(SUMMARIZE(ALL(Data),Data[Dates],"DateCount",COUNT([Dates])),[DateCount])

 

[JB2020]

I don't know if this is more efficient or not, but you could also use:

=AVERAGEX(SUMMARIZE(ALL(Data),Data[Dates],"DateCount",COUNT([Dates])),[DateCount])

Works great, Thanks Rory

 

[JB2020]

I don't know if this is more efficient or not, but you could also use:

=AVERAGEX(SUMMARIZE(ALL(Data),Data[Dates],"DateCount",COUNT([Dates])),[DateCount])

I had a bit of a play around with this today and here's what I came up with in the end:

=COUNTROWS(ALLSELECTED(Data))/COUNTROWS(ALLSELECTED(Calendar))

Just a quick follow up if you are able to offer any advice on this also? My data contains dates for 2019 and 2020 and is currently filtered for dates only in 2020. I have a measure which shows the count of Dates for the same weekday last year (Date - 364). Is there a way to create a grand average for these also, like the measure above? |So basically I need it to count all rows in the Data table where the date = all selected dates - 364.