I am trying to create a column that will display a measure value that I created if another column contains text but I am having problems where the measure is being recalculated based on another column in my sheet.
I created a 5 week average for my data based on filtering out only the last 5 weeks worth of data (Week Number: -5, -4, -3, -2, -1) into a seperate sheet. From there I took a measure that dynamically calculated that value correctly (say this value is '100').
I then went back into my main sheet which has 3 relevant columns (Discipline, Week Number, and 5 Week Average). What I want to do is have the column 5 Week Average display the value '100' any time Discipline contains text (such as PIPE).
The issue is that the measure will reference the Week Number in my main table instead of simply displaying the '100' value. (i.e. if Week Number = -1, the measure will only take the average of the values from -1 which ends up being like 85) Similarly, if the Week Number in my main table is not -5, -4, -3, -2, -1, the value will be left blank.
What I am trying to do is have a dynamic running average that will be applied to any row that contains the text PIPE.