Calculating daily average from sum of day

[JumoGra]

Hi There,

I'm trying to calculate the daily average of all calls offered, I started off by doing a sum but couldn't figure out how to then divide the sum by the count of column values for the period. I have included my DAX formula in terms of where I got up to. Please let me know if there's even a better way of doing this. Many thanks

AvgWTDCallsOffered =

    CALCULATE(

        SUM('Call Data'[Offered]),

        FILTER(

            ALL(CDCalendar),

            (CDCalendar[Last7Days] = MAX(CDCalendar[Last7Days])) &&

            (CDCalendar[CallDate] <= MAX(CDCalendar[CallDate]))))

[AnalyticsWizard · Accepted solution]

@JumoGra 

To calculate the daily average of all calls offered based on your existing DAX formula, you essentially want to divide the sum of calls offered by the number of days in the period. Your formula correctly sums the calls offered but does not account for the number of days over which to average.

AvgWTDCallsOffered =
VAR TotalCallsOffered = CALCULATE(
SUM('Call Data'[Offered]),
FILTER(
ALL(CDCalendar),
(CDCalendar[Last7Days] = MAX(CDCalendar[Last7Days])) &&
(CDCalendar[CallDate] <= MAX(CDCalendar[CallDate]))
)
)
VAR CountOfDays = CALCULATE(
DISTINCTCOUNT(CDCalendar[CallDate]),
FILTER(
ALL(CDCalendar),
(CDCalendar[Last7Days] = MAX(CDCalendar[Last7Days])) &&
(CDCalendar[CallDate] <= MAX(CDCalendar[CallDate]))
)
)
RETURN
DIVIDE(TotalCallsOffered, CountOfDays)

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[JumoGra]

This has worked perfectly, thank you so much!