Ranking Multiple columns in a matrix

[CaraJonesReadle]

Hello

I have a matrix that I am trying to show the top 3 Level 2 downtime areas and then show the top 3 reasons (level 3) for that area. I am using TOP N for the first column, from my research it seems that I should be using RANK for the second column but I am lost as to how I do that.

 I am a very novice user so any help would be greatly appreciated.

Thanks

Cara

[Rfranca]

hi, 

@CaraJonesReadle @Ashish_Mathur

I understood what you want
it is not necessary to create the TOP N or RANKX creates the columns and their levels and only classifies the values according to the image.

[Ashish_Mathur]

Hi,

What happens when you use Top N for the second column as well?  Share the link from where i can download your PBI file.

[CaraJonesReadle]

You can only use TOP N on one column, thats really my main issue.

Hopefully this works for sharing the file......

https://app.powerbi.com/groups/me/dashboards/d8750caa-4718-41f9-8db3-346e5f9df6e7

[Ashish_Mathur]

Hi,

I cannot download the file from here.

[CaraJonesReadle]

What is the best way yo share the file with you seeing as the link doesn't work? The only other way I know is via e-mail.

[Ashish_Mathur]

Hi,

Upload the file to Google Drive or any other such service and share the download link.

[CaraJonesReadle · Accepted solution]

Hi Ashish

Hopefully this works

https://drive.google.com/file/d/1U0DWUxqqb8Spd3OhaYwwyae-TqTf-4Gq/view?usp=sharing

Thanks

Cara

[Ashish_Mathur]

Hi,

You may download my solution file from here.

Hope this helps.

[CaraJonesReadle]

Thanks so much Ashish this is terrific.

Don't suppose you could explain how you managed to create the ranking filter, as I am sure that I will have similar situations occur on other reports I create.

[Ashish_Mathur]

You are welcome.  I created a rank measure using the RANKX formula to assign a rank to each Level3 based on Event duration.  I then dragged that measure to the visual filter section and applied a criteria of <=3.

[CaraJonesReadle]

HI Ashish

Your solution has been working brilliantly, thank-you again.

I am wondering though, I now want to have 3 columns each showing the top 3 issues based on duration. I have copied the rank x formula for the 3rd column that I want to add, however now column 2 losses its ranking when i apply this. How can I rectify this issue?

Thanks

Cara

[Ashish_Mathur]

You are welcome.  Share the file and tell me exactly what the problem is.

[CaraJonesReadle]

Here is the link

https://drive.google.com/open?id=1Dr-XRSW58BbE5x8iyqFHvbvLXci6ljct

In the tab called Overall, you can see a matrix that is showing 3 levels, level 2 and event descirption are only showing the top 3 issues where as the level 3 column shows all. What I am looking for is the Top 3 level 2 events by duration that then break into the top 3 level 3 events and then the top 3 evenet descriptions.

Thanks so much for your help 🙂

[Ashish_Mathur]

Hi,

Someone else will help you with solving this problem.