DAX Top and Bottom Calculations Across a Row

[wg]

Hello everyone,

I am working on a project where the data coming in is spread across multiple columns (see image below).

The problem that I have is that I need to rank the months across each key to get the Top 3 months and the Bottom 3 months. Basically, I need to get the average of the Top 3 months for a given Key value and the average of the Bottom 3 months for a given Key value.

What I want to end up with is this:

The Top 3 Month Average and the Bottom 3 Month Average would be used in other calculations. How can I accomplish this in Power BI or DAX? Thank you in advance.

[v-yuezhe-msft · Accepted solution]

@wg,

Please check DAX in the following PBIX file.

https://1drv.ms/u/s!AhsotbnGu1Nok3wnIFbIfQmQZA01

Regards,
Lydia

[wg]

Thank you, @v-yuezhe-msft

It looks like you did was I was debating about doing - pivoting the data to then rank. I really appreciate the response, as this gave me exactly what I needed.

I created a pivoted table based on the data as it exists in the current data table, used the formulas you shared with me in your sample file, and then joined the two tables together to allow filtering to work seamlessly.

Thanks again!