how to select top N records in power bi

0
votes

The data set is as follows

raw data set

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

result

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

2
powerbidax
Can you try this? First we summarize and then select top 2: TOPN(2, SUMMARIZE(EmployeeMarksTable, EmployeeMarksTable[Mark], "SumOfMarks", SUM(Mark)),SumOfMarks,0 ) – CleanBold
I tried the above one got the following error,The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. – Smart003

2 Answers

2
votes

Create a measure called rank:

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

Table with rank measure

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:

enter image description here

That will give you this:

enter image description here

1
votes

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!