Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculate average of 4 weeks dynamically based on filter selection

Hi Team, 

 

I have been trying to identify a way for calculating average of 4 weeks ( Sum[X] / 4 ) based on filter selection in slicer. 

 

On left hand side , we have table which shows Average for each week and Fiscal Year week . On right hand side, we have fiscal year week as a slicer . As shown in picture , 2024 W29 is selected in slicer. the expected result from a measure is to calculate 4 week average . for example, if 2024 w29 is selected in slicer , then 

 measure =  Sum ( 2024 W29 + 2024 W28 + 2024 W27 + 2024 W26 ) / 4 

2024 W29  = 13.8

2024 W28  = 14.0 

2024 W27 =  12.4

2024 W26 =  14.6 

 

o/p should be = ( 13.8+14.0+12.4+14.6) /4 

 

Similarly if 2024 W28 is selected in filter, then it should automatically calculates average as below . 

 

measure =  Sum (  2024 W28 + 2024 W27 + 2024 W26 + 2024 W25 ) / 4 

 

2024 W28  = 14.0 

2024 W27 =  12.4

2024 W26 =  14.6 

2024 W25 =  16.9 

 

o/p should be = ( 14.0+12.4+14.6 + 16.9 ) /4 

 

Please assist how a measure can be created to do above calculations. 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi, 
    I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

     

    Value sum: = 
SUM( data[value] )

     

    WINDOW function (DAX) - DAX | Microsoft Learn

     

    expected result measure: =
VAR _slicerselect =
    MAX ( slicer_calnedar[Start of Week] )
VAR _fourweeksperiod =
    WINDOW (
        1,
        ABS,
        4,
        ABS,
        FILTER (
            ALL ( 'calendar'[Year Week], 'calendar'[Start of Week] ),
            'calendar'[Start of Week] <= _slicerselect
        ),
        ORDERBY ( 'calendar'[Start of Week], DESC )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'calendar'[Start of Week] ) = _slicerselect,
        AVERAGEX ( _fourweeksperiod, [Value sum:] )
    )

     

     

7 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi, 
    I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

     

    Value sum: = 
SUM( data[value] )

     

    WINDOW function (DAX) - DAX | Microsoft Learn

     

    expected result measure: =
VAR _slicerselect =
    MAX ( slicer_calnedar[Start of Week] )
VAR _fourweeksperiod =
    WINDOW (
        1,
        ABS,
        4,
        ABS,
        FILTER (
            ALL ( 'calendar'[Year Week], 'calendar'[Start of Week] ),
            'calendar'[Start of Week] <= _slicerselect
        ),
        ORDERBY ( 'calendar'[Start of Week], DESC )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'calendar'[Start of Week] ) = _slicerselect,
        AVERAGEX ( _fourweeksperiod, [Value sum:] )
    )

     

     

  • SamWiseOwl's avatar
    SamWiseOwl
    Icon for Super User rankSuper User
    2 years ago

    Hi Anonymous 

    Looking for something like this?

     

    Average 4 weeks =
     AVERAGEX(--calculate the average
        TOPN(4 --capture top 4 rows
        ,CALCULATETABLE('List FW','List FW'[FW] <= SELECTEDVALUE(FW[Choose FW])) --Return rows less than slicer
        ,'List FW'[FW],DESC),[Values]) --Sort 4 rows by fW return top, divide by row count



    Showing included rows in calculation:

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi SamWiseOwl 
      Can you please share pbix file . The table notations you have used are slightly different and am not able to sync it with my tables .