Forum Discussion

Sachintha's avatar
Sachintha
Helper III
2 years ago
Solved

How to filter a visual by 4-week average

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 c...
Need Help
Show and Tell
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    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 )

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
2 years ago

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 )