Forum Discussion
Moving Average Over Group With Slicer
The table below shows the number of incidents raised by category. They are grouped by month/year with each month given a rank with 1 being most recent.
I have a bar chart that has calls raised and closed for each month with a line for calls outstanding at end of month and an MAA. The report is filtered to only show the top 12 ranked months with the hidden columns only used to calculate MAAs.
I'm trying to calculate an MAA for Calls Outstanding, but the report has a slicer so you can filter down to a specific category and I need the MAA to recalculate when a category is selected.
If I use the formula below it appears to calculate MAA correctly for all incidents but when a category is selected from the slicer, the MAA line still shows values for all incidents, not just the selected category.
I've tried replacing 'ALL' with 'ALLSELECTED' but as months ranked 13-23 aren't displayed in graph the MAA is incorrect.
Calls Outstanding MAA =
CALCULATE (
Sum('Incident History'[CallsOutstanding]),
FILTER (
ALL ( 'Incident History' ),
'Incident History'[MonthRank] <= MIN ('Incident History'[MonthRank]) + 11
&& 'Incident History'[MonthRank] >= MIN ( 'Incident History'[MonthRank] )
)
)/12
| CallYear | CallMonth | CallMonthStartDate | CallYearMonth | MonthRank | Category | CallsRaised | CallsClosed | CallsOutstanding |
| 2017 | 3 | 01/03/2017 00:00:00 | 17-03 | 3 | RMS | 0 | 0 | 0 |
| 2017 | 3 | 01/03/2017 00:00:00 | 17-03 | 3 | Website | 11 | 11 | 8 |
| 2017 | 3 | 01/03/2017 00:00:00 | 17-03 | 3 | Website Change Request | 0 | 0 | 0 |
| 2017 | 4 | 01/04/2017 00:00:00 | 17-04 | 2 | Applications | 92 | 92 | 18 |
| 2017 | 4 | 01/04/2017 00:00:00 | 17-04 | 2 | Hardware | 21 | 24 | 5 |
| 2017 | 4 | 01/04/2017 00:00:00 | 17-04 | 2 | Miscellaneous | 55 | 54 | 8 |
| 2017 | 4 | 01/04/2017 00:00:00 | 17-04 | 2 | New Software Request | 0 | 0 | 0 |
| 2017 | 4 | 01/04/2017 00:00:00 | 17-04 | 2 | Other Change Request | 0 | 0 | 0 |
| 2017 | 4 | 01/04/2017 00:00:00 | 17-04 | 2 | RMDB | 0 | 1 | 2 |
| 2017 | 4 | 01/04/2017 00:00:00 | 17-04 | 2 | RMS | 0 | 0 | 0 |
| 2017 | 4 | 01/04/2017 00:00:00 | 17-04 | 2 | Website | 3 | 5 | 6 |
| 2017 | 4 | 01/04/2017 00:00:00 | 17-04 | 2 | Website Change Request | 0 | 0 | 0 |
| 2017 | 5 | 01/05/2017 00:00:00 | 17-05 | 1 | Applications | 118 | 123 | 13 |
| 2017 | 5 | 01/05/2017 00:00:00 | 17-05 | 1 | Hardware | 57 | 55 | 7 |
| 2017 | 5 | 01/05/2017 00:00:00 | 17-05 | 1 | Miscellaneous | 35 | 37 | 6 |
| 2017 | 5 | 01/05/2017 00:00:00 | 17-05 | 1 | New Software Request | 0 | 0 | 0 |
| 2017 | 5 | 01/05/2017 00:00:00 | 17-05 | 1 | Other Change Request | 0 | 0 | 0 |
| 2017 | 5 | 01/05/2017 00:00:00 | 17-05 | 1 | RMDB | 0 | 2 | 0 |
| 2017 | 5 | 01/05/2017 00:00:00 | 17-05 | 1 | RMS | 0 | 0 | 0 |
| 2017 | 5 | 01/05/2017 00:00:00 | 17-05 | 1 | Website | 13 | 16 | 3 |
| 2017 | 5 | 01/05/2017 00:00:00 | 17-05 | 1 | Website Change Request | 0 | 0 | 0 |
How can I get the MAA to work correctly when a catgeory is selected in the Slicer
Any help is greatly appreciated.
Thanks
Craig
3 Replies
v-jiascu-msftMicrosoft Employee
Hi Craig,
According to my test, Allselect() or Allexcept() should work. If the minimum of the selected category is 1, monthrank 13-23 won't display. Hope this would help.
4Calls Outstanding MAA = CALCULATE ( SUM ( 'Incident History'[CallsOutstanding] ), FILTER ( ALLEXCEPT ( 'Incident History', 'Incident History'[Category] ), 'Incident History'[MonthRank] <= MIN ( 'Incident History'[MonthRank] ) + 11 && 'Incident History'[MonthRank] >= MIN ( 'Incident History'[MonthRank] ) ) )Best Regards!
Dale
Hi Dale,
first apologies for the late reply, and thanks for taking time to reply.
Using the ALLEXCEPT option seems to base the MAA on all other categories - in the updated chart, you can see the MAA has a much higher value than you'd expect, much higher than the Calls Outstanding at the end of each month.
Thanks
Craig
- v-jiascu-msft
Hi Nev433,
Could you please mark the proper answer if it's convenient for you? That will be a help to others.
Best Regards!
Dale
