Forum Discussion

wg's avatar
wg
Regular Visitor
8 years ago
Solved

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).

 

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.

 

2 Replies

    • wg's avatar
      wg
      Regular Visitor
      8 years ago

      Thank you, Anonymous

       

      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!