Best 6/8-Year Average

[kristenmcnelly]

Hello,

I have columns for 2022 Production, 2021 Production, 2020 Production, 2019 Production, 2018 Production, 2017 Production, 2016 Production, and 2015 Production. I would like to add a column to the table for the best 6/8-year average (average of max 6 years).

I have been able to create the best 6/7-year average by subtracting the minimum, but I'm not sure if I'm able to have 2 minimums. Please let me know how to do this or if there is a better way to do this.

Thanks,

Kristen

[v-tangjie-msft]

Hi @kristenmcnelly ,

According to your description, you want to calculate the best 6/8 average, right? Here are my steps you can follow as a solution.

(1)This is my test data.

(2)Click “Home” ->” Transform data”.

Sort the [Production] column in descending order.

(3) Add an index column.

(4) We can create  a measure.

Measure = CALCULATE(SUM('Table'[Production]),FILTER(ALL('Table'),'Table'[Index]>=1 && 'Table'[Index]<=6))/8

(5) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[kristenmcnelly]

My data is formatted as the production for each year as the columns and multiple rows of people, so I don't think it will work the same way.

[amitchandak]

@kristenmcnelly ,

New Column=

var _table = {[2022 Production], [2021 Production], [2020 Production], [2019 Production], [2018 Production], [2017 Production], [2016 Production]}

var _add = addcolumn(_table, "Rank", Rankx(_table, [Value],,asc,dense)

return

averageX(filter(_table, [Rank]>1), [Value])

[kristenmcnelly]

Thank you for your response! I'm not sure what [Value] should be. I suspect it should be Production, but Production is broken down by year, it is not one field.