How to filter a visual by 4-week average

[Sachintha]

I have a data set that looks like below (sample data posted at the end of the post). It's the availability (as a percentage) of certain units by work week. Each unit belongs to a group that doesn't change. In my actual data set I'll have data going back years.

WW - Work Week, in the YYYY-WW format.
Unit - Units for which the percentage is listed.
Group - The group the unit belongs to. Doesn't change.
Active - Active % of the unit for the week.

I would like to display this data in a Matrix, seen as below, for the most recent 4 weeks.

To achieve this, I've set up Rows, Columns, ad Values in the Matrix visual as follows, along with a WW filter to filter out only the most recent 4 WWs.

This works fine so far, but my next requirement is to filter and display only the groups whose 4-week Active Average is below 90%. What this means is that in the above example, I only want to display data for G2 and G3 since they're the only two groups whose 4-week average is under 90%. Also, it's critical to mention that I want to display ALL units for a particular group if the group's average is under 90%, even if a particular unit's is over 90% (as is the case of Unit = F, where 4 weeks average of which is 97.25%).

But, simply adding a filter to the visual where average < 90% doesn't work as filters are calculated for each row, so it excludes the individual units whose average is under 90%. If I did so, this is what I get which is not the desired result:

How do I get the intended result - I'm guessing I need to create some measure that calculates the 4 week average for a group, but I'm not quite sure.

WW,Unit,Group,Active
2023-39,A,G1,0.98
2023-39,B,G1,0.56
2023-39,C,G1,0.55
2023-39,D,G2,0.56
2023-39,E,G2,0.48
2023-39,F,G3,0.56
2023-39,G,G3,0.98
2023-39,H,G3,0.56
2023-40,A,G1,0.94
2023-40,B,G1,1
2023-40,C,G1,0.95
2023-40,D,G2,0.91
2023-40,E,G2,0.65
2023-40,F,G3,1
2023-40,G,G3,0.45
2023-40,H,G3,0.65
2023-41,A,G1,0.99
2023-41,B,G1,0.83
2023-41,C,G1,0.96
2023-41,D,G2,0.34
2023-41,E,G2,0.95
2023-41,F,G3,0.98
2023-41,G,G3,1
2023-41,H,G3,1
2023-42,A,G1,1
2023-42,B,G1,0.89
2023-42,C,G1,0.95
2023-42,D,G2,0.45
2023-42,E,G2,0.34
2023-42,F,G3,0.92
2023-42,G,G3,0.44
2023-42,H,G3,0.92
2023-43,A,G1,0.94
2023-43,B,G1,0.99
2023-43,C,G1,0.56
2023-43,D,G2,0.95
2023-43,E,G2,0.34
2023-43,F,G3,0.99
2023-43,G,G3,1
2023-43,H,G3,0.56

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like but please check the below picture and the attached pbix file.

Active Average: = 
    AVERAGE ( Data[Active] )

Active Average recent 4 wks and group result below 0.9: =
VAR _groupavgrecentfourweeks =
    CALCULATE (
        [Active Average:],
        WINDOW ( 1, ABS, 4, ABS, ALL ( Period[WW] ), ORDERBY ( Period[WW], DESC ) ),
        ALL ( Unit[Unit] )
    )
VAR _averagerecentfourweeks =
    CALCULATE (
        [Active Average:],
        KEEPFILTERS (
            WINDOW ( 1, ABS, 4, ABS, ALL ( Period[WW] ), ORDERBY ( Period[WW], DESC ) )
        )
    )
RETURN
    IF ( _groupavgrecentfourweeks < 0.9, _averagerecentfourweeks )