Dynamic TopN & Ranking in Power BI

[Asamadi]

How may i create a Table  ( Matrix) like the below image, This is my Excel Sample Data: https://www.dropbox.com/s/ndg0t0tfpn4bt6d/001.xlsx?dl=0

[erik_tarnvik · Accepted solution]

I would first use the query editor to transform your input data so you have only one score per line in your TestData table. Mark the four score columns and use "Unpivot columns", rename the resulting Attribute/Value columns to get this:

Then define two measures:

AverageScore = AVERAGE(TestData[Test Score])

StudentRank = RANKX(ALL(TestData[Name],TestData[Topic]), Calculate(Average(TestData[Test Score]),ALLEXCEPT(TestData,TestData[Name],TestData[Topic])),,DESC,Skip)

Populate a table visual with Topic, Name, AverageScore and StudentRank and define a visual filter on StudentRank to only show value less or equal to 5. Result:

Now, obviously this is 6 values but given the subject matter I assume you don't want to leave tied students out of the top 5.

I am sure there are better solutions, I am not a guru and would love to learn more myself!

[v-yuezhe-msft · Accepted solution]

@Asamadi,

In addition to other’s result, to display the bottom  5 performance performing employees, create the following measure. Then drag the measure to your Matrix visual and set its values to “less than or equal to 5”.

rank2 = RANKX(ALL(TestData[Name],TestData[Topic]), Calculate(Average(TestData[Test Score]),ALLEXCEPT(TestData,TestData[Name],TestData[Topic])),,ASC,Skip)

Besides, when creating the Matrix visual, turn off “Stepped layout” option under Row headers and drill down to the lowest level, you will get expected result.

Regards,
Lydia

[Asamadi]

Hello thanks for your response

may you send me the pbix file?

my email:

akbar.samadi110@gmail.com

[v-yuezhe-msft]

@Asamadi,

You can download the PBIX file from the following link.
https://1drv.ms/u/s!AhsotbnGu1Nogn5wQ3lO61iwIpAz

Regards,
Lydia

[erik_tarnvik · Accepted solution]

I would first use the query editor to transform your input data so you have only one score per line in your TestData table. Mark the four score columns and use "Unpivot columns", rename the resulting Attribute/Value columns to get this:

Then define two measures:

AverageScore = AVERAGE(TestData[Test Score])

StudentRank = RANKX(ALL(TestData[Name],TestData[Topic]), Calculate(Average(TestData[Test Score]),ALLEXCEPT(TestData,TestData[Name],TestData[Topic])),,DESC,Skip)

Populate a table visual with Topic, Name, AverageScore and StudentRank and define a visual filter on StudentRank to only show value less or equal to 5. Result:

Now, obviously this is 6 values but given the subject matter I assume you don't want to leave tied students out of the top 5.

I am sure there are better solutions, I am not a guru and would love to learn more myself!