Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
wg
Regular Visitor

DAX Top and Bottom Calculations Across a Row

Hello everyone,

 

 

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

Sample dataset.PNG

 

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:Sample dataset2.PNG

 

 

 

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.

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@wg,

Please check DAX in the following PBIX file.

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@wg,

Please check DAX in the following PBIX file.

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!