Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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 👍
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 👍
This has worked perfectly, thank you so much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
85 | |
77 | |
69 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |