Average Calls per day, grouped by month

[Anonymous]

How do you calculate the average of [wholds] in the month? Activity date is connected to a date table.

Idealy, Id like to be able to know how many [wholds] a Sales rep made per day based off the number of days in the month. 

Essentially it would be the disctinct count of wholds in the month divided by the number of days in the month, and I could put it into a matrix/table that would know which month the calculation corresponds to. 

Wholds	Activity Date
random numbers	04-10-22
random numbers	04-12-22
random numbers	03-06-22
random numbers	02-28-22

[v-jayw-msft]

Hi @Anonymous ,

Please check the formula.

Meaasure =
VAR count_wholds =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Wholds] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Activity Date].[Year] = SELECTEDVALUE ( 'calendar'[Date].[Year] )
                && 'Table'[Activity Date].[Month] = SELECTEDVALUE ( 'calendar'[Date].[Month] )
        )
    )
RETURN
    count_wholds / COUNTROWS ( 'calendar' )

Best Regards,

Jay

[Anonymous]

It did not work, I take it 'calendar' is the calendar table I have attached to the activity date.

[tamerj1 · Accepted solution]

Hi @Anonymous 

You can use

DIVIDE ( TableName[Wholds], COUNTROWS ( 'VALUES ( "Date'[Date] ) ) )

[Anonymous]

This worked if the columns in the matrix are from the Date/Calendar table used and not from the Date from the Datatable.