Remove Filters within Window functions

Question

Hi,&nbsp; I am calculating moving average of 3 weeks on a YTD weekly sales data.&nbsp;Sample Example -&nbsp; Assuming hypothetical data below arranged by weeks from latest weeks as 0 to prior weeks decremented by -1.In my Visual i want to display latest 7 weeks sales and their moving average.&nbsp;Any idea how to ignore filters on visual or page while using a window function&nbsp;---------INPUT DATA---------Weeks&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sales&nbsp; &nbsp; &nbsp; Moving Avg (Rolling 3 weeks)0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;60-1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;60&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;70-2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;70&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;80-3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;80&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;90-4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;90&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100-5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;110-6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;110&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 120-7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;120&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;130-8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;130&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;140&nbsp;&nbsp;-9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 140&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 150&nbsp;&nbsp;------------------------Expected OUTPUT-------------------&nbsp;Weeks&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sales&nbsp; &nbsp; &nbsp; Moving Avg (Rolling 3 weeks)0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;60-1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;60&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;70-2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;70&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;80-3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;80&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;90-4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;90&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100-5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;110-6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;110&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;120&nbsp;--------------------OUTPUT Achieved --------------&nbsp;&nbsp;Weeks&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sales&nbsp; &nbsp; &nbsp; Moving Avg (Rolling 3 weeks)0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;60-1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;60&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;70-2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;70&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;80-3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;80&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;90-4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;90&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100-5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;110-6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;110&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;110&nbsp;Have a filter on visual to include weeks till -6,&nbsp; after using window function as below&nbsp; value for moving average for week -6 is same 110 but ideally we expect 120.&nbsp;Measure =&nbsp; AverageX(&nbsp; Window(-2, REL,0, REL, SUMMARIZE( ALL(DATA), [Weeks]), Order By ([Weeks], ASC) ), SUm(SAles) )&nbsp;&nbsp;

sanalytics · Accepted Answer

pandbh1989&nbsp;Averagex is an iterator. It triggers row context. We need to do the context transition. So, only SUm(SAles) should not work properly. Wrap this thing with CALCULATE or create a separate measure for Sales.Based on your scenario, you need to modify the window settings as well.Try the below measure.Moving Aveage = 
  AVERAGEX(  
    WINDOW(
    0, REL,
    2, REL,
     SUMMARIZE( ALL('Table'), [Weeks]), 
     ORDERBY([Weeks], ASC) ), [Total Sales]
  )Check out the below screenshot if it works&nbsp;&nbsp;Hope it will help you.&nbsp;RegardssanalyticsIf it is your solution then please like and accept it as solution

Forum Discussion

Remove Filters within Window functions

1 Reply

Related content

Caclulate function over riding filter?

Removing date filter

filter function

remove button colour fill function

Remove visual filter context