Forum Discussion

JohnCowx's avatar
JohnCowx
Frequent Visitor
6 years ago
Solved

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

  • az38's avatar
    az38
    6 years ago

    Hi JohnCowx 

    try smth like

    ALLSELECTED(dayMax[WEEKDAY]) or ALLSELECTED(dayMax)

3 Replies