Forum Discussion
moving average help
Hi gilmore_staci ,
Thank you for sharing the formula. The error with AVERAGE occurs because Dealer Resolution Rate is a measure, not a column. AVERAGE works only with columns, so Power BI doesn’t allow it here. This is also why AVERAGEX doesn’t return an error but gives zeros, as it iterates row by row while your percentage is already calculated as a measure.
Since the value is already aggregated, there’s no need to average it again over the Case table. Instead, the moving average should evaluate the measure over the last 3 months using the date context, allowing Power BI to calculate the measure across the previous 3 months based on the date filter, rather than averaging row-level values, which can lead to zeros.
Hope this helps....
Hi gilmore_staci ,
Based on the screenshots, it seems the relationship between your Case date and Calendar date is inactive. This causes the visual to display 0s when you set the X-axis to the Calendar table, as it doesn’t filter the Case table correctly.
Try these steps.
1. Verify that the relationship between Case[Date Incident Resolved] and Calendar[Date] is active.
2. Use a Month Year column from the Calendar table for the X-axis instead of a text field from the Case table.
3. Note that using the full Date field will show daily values in Power BI, so a Month-Year column will keep it grouped by month.
Once the relationship is active and the axis is set from the Calendar table, your monthly values and 3 month moving average should display correctly.
Thanks for sharing more details and your insights.
V-yubandi-msft thank you, I'm getting closer. The moving average is giving zero for the data points but the line seems correct. The only thing I can see is the AVERAGE in the calculation. If I use AVERAGE it says I can't use Dealer Resolution Rate its either not in the table or can't be used in the expression. It doesn't give an error if I use AVERAGEX, but also gives me zeros
Dealer Resolution Rate(percentage) =
Hi gilmore_staci ,
Thank you for sharing the formula. The error with AVERAGE occurs because Dealer Resolution Rate is a measure, not a column. AVERAGE works only with columns, so Power BI doesn’t allow it here. This is also why AVERAGEX doesn’t return an error but gives zeros, as it iterates row by row while your percentage is already calculated as a measure.
Since the value is already aggregated, there’s no need to average it again over the Case table. Instead, the moving average should evaluate the measure over the last 3 months using the date context, allowing Power BI to calculate the measure across the previous 3 months based on the date filter, rather than averaging row-level values, which can lead to zeros.
Hope this helps....