topic Re: Average per row based on slicer in DAX Commands and Tips

Average per row based on slicer

danielcontente — Tue, 10 Jan 2023 14:51:49 GMT

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

Re: Average per row based on slicer

AlB — Tue, 10 Jan 2023 16:01:36 GMT

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.

Re: Average per row based on slicer

danielcontente — Tue, 10 Jan 2023 16:03:27 GMT

Hi, the table is in the first comment (there is a picture).

Re: Average per row based on slicer

danielcontente — Tue, 10 Jan 2023 16:04:05 GMT

the previous two years are dynamic

Re: Average per row based on slicer

AlB — Tue, 10 Jan 2023 16:07:24 GMT

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.

Re: Average per row based on slicer

FreemanZ — Tue, 10 Jan 2023 16:07:34 GMT

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

Re: Average per row based on slicer

danielcontente — Tue, 10 Jan 2023 17:13:23 GMT

Dynamic based on the current year.

Re: Average per row based on slicer

Anonymous — Wed, 11 Jan 2023 05:21:39 GMT

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