Forum Discussion
Filtering in measure CALCULATION
Hi
I have a calculation which I cannot get the filtering right for.
I have the following tables joined on Date[Date]=dayMax[day]
I want a measure to calculate the AVERAGE of MaxOccupiedPer for each WEEKNUM but I want the user to be able to select the WEEKDAY values (1,2,3,4,5) from the Fliters pane. So the ave only applies to those days selected.
This is the formula I have used:
WEEK_ThisMax =
CALCULATE(AVERAGE(dayMax[maxOccupiedPer]),
FILTER(ALL(dayMax),
dayMax[Max_WeekNum]=MAX('Date'[Weeknum])&&dayMax[MAX_YYYYMM]=MAX('Date'[YYYYMM])))
I can get the AVE correct if all WEEKDAY's are selected - and I seem to need to use the FILTER(ALL(dayMax) to be able to ave across the WEEKNUM.
If I only select WEEKDAY = 2,3,4 then I still get the same average across the whole 5 days. I understand that I want a context filter on the initial data to get only the 3 days (2,3,4) and then apply an average - but I can't seem to get it right.
Thanks
3 Replies
- amitchandakSuper User
JohnCowx , For the week, refer if my blog can help
- JohnCowxFrequent Visitor
Thank you amitchandak - I am ok with the week logic, although helpful to see the options.
My key issue is around creating an ave for the days selected by the user.
- az38Community Champion