how to select top N records in power bi

Question

The data set is as follows

i have written a measure which will make the sum of mark column

DAX_mark_sum=sum(mark)

I got the following result as expected when used the table visual

Now I need the top 2 records order by mark sum desc

To achieve the result i have tried like 1. mark the measure as "don't summarize" 2. visual filters----top N selected---> Placed 2 records.

but i got duplicates....Please help me with this one

thanks in advance

Answer 1

If you are trying to break ties in your 'Top N' ranking try something like this:

Top 2 Emp Id :=
CALCULATE ( TOPN ( 2, VALUES ( 'Table'[Emp_Id] ), [DAX_mark_sum] + RAND () ) )

Rand() will add a number between 0 and 1 to your [DAX_mark_sum] calculation, at random, eliminating the possibility of a tie. Kind of a hack but it should work.

Hope it helps!

Answer 2

Create a measure called rank:

Rank = RANKX ( ALLSELECTED ( Table1[emp name] ), CALCULATE ( SUM ( Table1[mark] ) ) )

Click on the table, go into 'Visual level filters' find the rank column and set a condition that shows items when the value is less than 3:

That will give you this: