Forum Discussion

danielcontente's avatar
danielcontente
Icon for Helper II rankHelper II
3 years ago
Solved

Average per row based on slicer

Hi all, 

 

I have created a table and would like to create a measure that will average the values per row for the 2 previous years.

In the below table, the result for WEEK 41, for example, would be 487,003 (average of 2020 and 2021)

 

Thanks in advance 

 

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  danielcontente ,

     

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _today=YEAR(TODAY())
var _1year=
SUMX(FILTER(ALL('Table'),'Table'[Year]=_today -1&&'Table'[Weeknumber]=MAX('Table'[Weeknumber])),[Value])
var _2year=
SUMX(FILTER(ALL('Table'),'Table'[Year]=_today -2&&'Table'[Weeknumber]=MAX('Table'[Weeknumber])),[Value])
return
DIVIDE(
    _1year+_2year,2)

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

7 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi danielcontente 

    1. Can you share a sample of the relevant tables in your model? Or, ideally, the pbix?

    2. How are the "2 previous years" determined? Is it always 2020 and 2021 or dynamic?

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    3 years ago

    I cannot copy the data from a picture

    Dynamic based on what? Why not 2022 and 2021?

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi danielcontente 

    try like:

    Average = 
VAR _2020 = CALCULATE(SUM(TableName[Value]), YEAR(TableName[Date])=2020)
VAR _2021 = CALCULATE(SUM(TableName[Value]), YEAR(TableName[Date])=2021)
RETURN
(_2020+_2021)/2

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  danielcontente ,

     

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _today=YEAR(TODAY())
var _1year=
SUMX(FILTER(ALL('Table'),'Table'[Year]=_today -1&&'Table'[Weeknumber]=MAX('Table'[Weeknumber])),[Value])
var _2year=
SUMX(FILTER(ALL('Table'),'Table'[Year]=_today -2&&'Table'[Weeknumber]=MAX('Table'[Weeknumber])),[Value])
return
DIVIDE(
    _1year+_2year,2)

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly