cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heatheat52 Visitor
Visitor

Ranking multiple columns

Hi Power BI community. I am relatively new to Power BI so bear with me. 

 

I have a table of data where each row represents a production run on a specific by a specific team on a specific model etc. There are many different defects that can occur and they are each respresented with a column in the table. For example, a production run on 9/9/19 could have a value of 200 in the 'Input' column, a value of 2 in the 'Defect 1' column, and a value of 10 in the 'Defect 7' column. The rest of the Defect columns in that row would have a value of zero in them.

 

I have a visual that shows the frequency that each specific defect occurs according to a date hierarchy. Say there are 10 defects. I would like to be able to make the visual only show the top three defects for a specific date range determined by a slicer.

 

I have looked into this and could only find information relating to the RANKX function which ranks values in a column. Since I need to rank actual columns by their sum this was not helpful to me. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Ranking multiple columns

Hello @heatheat52 

The only way I know of to do what you are looking for is to unpivot the defects informatoin so you end up with 2 columns, defect type and value.  Then you can do the ranking on the value column.

Top 3 defects = 
VAR RankingContext = VALUES ( Defects[Defect] )
RETURN
CALCULATE(
    [Defect Amount],
    TOPN ( 3, ALL ( Defects[Defect] ), [Defect Amount] ),
    RankingContext
)

Top3Defects.jpg

I have attached my sample file for you to look at.

View solution in original post

1 REPLY 1
Super User
Super User

Re: Ranking multiple columns

Hello @heatheat52 

The only way I know of to do what you are looking for is to unpivot the defects informatoin so you end up with 2 columns, defect type and value.  Then you can do the ranking on the value column.

Top 3 defects = 
VAR RankingContext = VALUES ( Defects[Defect] )
RETURN
CALCULATE(
    [Defect Amount],
    TOPN ( 3, ALL ( Defects[Defect] ), [Defect Amount] ),
    RankingContext
)

Top3Defects.jpg

I have attached my sample file for you to look at.

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 276 members 3,128 guests
Please welcome our newest community members: