Need help with measure

[Radhika2605]

Hi Everyone,

I am working on an olympics data set and I am trying to find the top 10 atheletes using dax measure but instead of giving 10 results it is returning more than 10 results. 

Dax measure used- 

Top N Atheletes with maximum medals =

CALCULATE(

[Total Medals],

FILTER( VALUES( Athlete[Name] ),

RANKX( ALL( Athlete[Name] ), [Total Medals], , DESC ) <= 10 )

Here are the results-

Is it because the total number of medals has duplicate values or there is something wrong in my logic. Could you please guide me as to how can I get exactly 10 values even when total number of medals won by atheletes is duplicated.

My model is - 

Thanks

Radhika

[ryan_mayu · Accepted solution]

@Radhika2605 

that depends on how you want to choose the top 10 when total medals is dpulicated.

let's say by name.

_name=max( Athlete[Name] )

_rank=RANKX( ALL( Athlete[Name] ), [Total Medals], , DESC ) +RANKX( ALL( Athlete[Name] ), _name,DESC)/10

then you will get no duplicated rank.

[Radhika2605]

Hi Ryan,

One last thing on this one - when I am trying to create a stacked bar chart for these top 10 athletes and trying to bring the medal type(gold, silver and bronze) in the legend field then instead of showing 10 values it shows a lot of athletes. Left side is the measure we just created and right side is all the athelets and their medals. Even the distribution of medals shown in our meausre looks to be incorrect as it is not showing the bronze measures. This only happens when I bring medal type in legend.

What should I do for this?

Thanks

Radhika

[ryan_mayu]

@Radhika2605 

i did a test. maybe you can create a new table and create the visual based on the new table.

Table = TOPN(10,'Table',RANKX(all('Table'[name]),rankx(ALL('Table'[name]),[_sum],,DESC)+RANKX(all('Table'[name]),[_name],,ASC)/10,,ASC))

[Radhika2605]

@ryan_mayu Thanks so much for this, I will give it a try.

Thanks

Radhika

[ryan_mayu · Accepted solution]

@Radhika2605 

that depends on how you want to choose the top 10 when total medals is dpulicated.

let's say by name.

_name=max( Athlete[Name] )

_rank=RANKX( ALL( Athlete[Name] ), [Total Medals], , DESC ) +RANKX( ALL( Athlete[Name] ), _name,DESC)/10

then you will get no duplicated rank.

[Radhika2605]

Hi @ryan_mayu 

Thank you so much for your prompt response.

Could you please explain the logic you used a bit as I am unable to comrehend it (sorry still at beginner). Also, I tried to use what you suggested but it is giving error as shown in the screenshot below: 

Thanks

Radhika

[ryan_mayu]

@Radhika2605 

here is a workaround for you. pls see the attachment below

[Radhika2605]

Hi @ryan_mayu,

Thanks for you effor in sending the solution. I did tried it but the issue is it is still not giving the correct result. the result which I am getting is on the left. 

Right hand table is the table which shows the total number of medals won and I have arrange it in descending order according to the number of medals. It shows that the highest medal won is 58, then 39 and then 38. 

But the tabel on the left only has values for athelete names starting from alphabet A. I think it is arranging it in alphabetical order or something. 

how should the solution which you have provided needs to be refined for it to work properly? 

Thanks in advance

Radhika

[ryan_mayu · Accepted solution]

@Radhika2605 

it looks you have hundreds of names. pls try to change /10 to /100 or /1000

[Radhika2605]

Hi @ryan_mayu 

You are a legend!!

It worked, thank you so much for your assistance.

Thanks

Radhika