Forum Discussion

blake1506's avatar
blake1506
Frequent Visitor
3 years ago

Rolling Average Responding to Only Some Filters

This has had me stumped for weeks. I am trying to create a rolling average measure that responds to some filters but not others. 

 

Below you can see the data. For each row, if red is 0, I want to use that number to calculate a rolling average. If red is 1, the row is disregarded. There are 2 brands each year and 3 departments with their toy name attached. Count is the number of units we have.

This is what a working version of my report screen looks like. I can calculate percent of total without breaking the Denominator out separately, but it just helps me stay organized. I have it on the screen purely for reference of what the Denominator is calculating.

On the report view, I want to be able to filter down as shown below. I want to filter by brand, Dept or Toy, and the Full Years I want the rolling average calculated upon.

From the view above, I would like to gather what the rolling average of Count is from a two year span, while filtering its Brand and Dept. I can see that this is 6 both in my card and by doing the math from the count column of the table. However, the percent of total needs to be calculated without taking my Toy or Dept filter into account. The Denominator measure should sum the Rolling Average of each Toy or Dept for the selected Years and Brands.

We know the rolling average for Fireman is 6 as shown in previous pictures. The Denominator in this case should be 10.5 (((8+1)/2) + ((3+9)/2)). Superhero should not be accounted for because Red = 1. Therefore, the Percent of Total should be 57.14% (6/10.5).

 

Ultimately, I want the Percent of Total measure to divide the rolling average of the selected Toy/Brand (for a given brand in a 2 year span) by the sum of all Toy/Dept rolling averages in this same given brand and 2 year span where red=0.

 

Below are the current formulas for my measures. Any help is much appreciated!

 

Rolling Average = CALCULATE(AVERAGEX(Sheet1,AVERAGE(Sheet1[Count])),DATESINPERIOD(Sheet1[Full Year],LASTDATE(Sheet1[Full Year]),-2,YEAR))
 
Denominator = CALCULATE(SUMX(Sheet1,Sheet1[Count]),Sheet1[Count] > 0,ALLSELECTED(Sheet1[Full Year]),ALL(Sheet1[Dept]),ALL(Sheet1[Toy]))
 
Percent of Total = DIVIDE([Rolling Average],[Denominator])

1 Reply

  • blake1506's avatar
    blake1506
    Frequent Visitor

    Hey folks, just curious if anyone has any idea how to tackle this. Thanks!