Average count per day per person

[ggoode]

I was able to find some similar questions to mine, but have not been able to solve my problem.

Model data:

Name	Date
John Smith	9/1/2023 5:30:19 PM
John Smith	9/1/2023 3:30:19 PM
John Smith	9/2/2023 5:30:18 PM
Jane Smith	9/1/2023 3:30:12 PM

So what I would want is an average count per day. 

Since John had 2 one day and 1 the other day, his average per day would be 1.5. 

Jane would average 1. 

I was thinking I needed to use AVERAGEX, this is my current measure that is not working:

Measure = AVERAGEX (

    ALL ( Table1[date] ),

    CALCULATE ( COUNTROWS ( VALUES ( Table1[name] ) ) )

)

Any help is appreciated

Thank you

[ggoode]

This seems to work great, I guess it needed a more complicated measure than I thought. Thank you much

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Expected result measure: = 
VAR _t =
    SELECTCOLUMNS (
        ADDCOLUMNS ( Data, "@dateformat", FORMAT ( Data[Date], "yyyymmdd" ) ),
        Data[Name],
        [@dateformat]
    )
VAR _groupbytable =
    GROUPBY ( _t, [@dateformat], "@countrow", SUMX ( CURRENTGROUP (), 1 ) )
RETURN
    AVERAGEX ( _groupbytable, [@countrow] )