TOP N not working

[cham]

Hi,

I want to get the TOP 5 driver numbers according to the amount. I tried it as below but its not working. Can anyone help me to do this? 

[Ashish_Mathur]

Hi,

Try these measures

Total value = SUM(Table[Amount])

Rank by Driver = RANKX(ALL(Table[Driver]),[Total value])

Drag Driver and Total value measure to your visual.  Filter the second measure on Less than equal to 5.

Hope this helps.

[cham]

Hi,

What you mean by Filter the second measure on Less than equal to 5? 

See the below pic. Cannt we use TOP N filter to do this?

[Ashish_Mathur]

Yes, The TopN should also work.  Try it.  If it does not, then share the link from where i can download your PBI file.

[cham]

Hi @Ashish_Mathur 

I tried TOP N but its not working that's why I asked it from community. 

I used below two column in my report ,

Driver No	Amount
12	9876
14	2345
13	1234
12	4456
12	3221
12	1111
15	23345
15	3456
12	22224
12	1234
16	2214
16	1111
16	3345

[Ashish_Mathur]

Hi,

It works fine for me.  See the screenshot.

[cham]

Hi @Ashish_Mathur ,

I have a Driver No field with another measure I want to get top 5 drivers according to that measure. How can I get that?

[cham]

Hi @Ashish_Mathur 

Please download the below file and check. I creted a table and created to N but its not working. Its showing more than 20 Driver Numbers

https://drive.google.com/file/d/1zkH2F7ZLosCyORMGP6jfRbsI5-aapeVG/view?usp=sharing

[jdbuchanan71]

You could take into account the number of responses along with the avg score to get a ranking.  I have added the calc and adjusted the matrix to give you an idea of what I had in mind.

[TomMartens]

Hey @jdbuchanan71 ,

smart move!

[cham]

Hi @jdbuchanan71 ,

Can you please explain me the measure you used for calculate the ranking?

[jdbuchanan71]

Sure

If [Dirver Number] = 0 or [Driver Number] = BLANK() ignore the row in the ranking.

Otherwise rank the drivers based on [Avg Score] * [Response Count].

Basically, if two drivers have the same average score rank the one with more responses higher.

[cham]

Hi @jdbuchanan71 

Cant we put rank the drivers based on [N]? 

Also if I add the coutry into it numbers are getting increase. Why is that? 

[jdbuchanan71]

@cham 

You can apply a filter to a visual of TopN based on a measure.  For ranking you want to do it in BottomN so it will go from lowets (1, which is the best rank) up.

Not sure what you mean about country, can you share your data including the country and show the problem and expected result?

[cham]

Hi @jdbuchanan71 ,

https://drive.google.com/file/d/10a5XQYojfl9c-4unh1ZBbmDNs9E2ekh5/view?usp=sharing

Please find the above link to access the file. I want to show Top 20 "N" value accoding to the drive and the country. 

I am confusing why you create a response count aand ave score for this? I want to get top 20 drivers in countries which show top 20 "N values"

Hope this explain you my question. 

[jdbuchanan71]

@cham 

There are 25 drivers in Canada all with N = 100.  Which 20 of those do you want?

[cham]

Hi @jdbuchanan71 

Did you find any way to solve this? Can you please help me with this?

[jdbuchanan71 · Accepted solution]

Hello @cham 

I believe I did find a way to solve this.  I had to add a drivers table to use as a anchor for the rankings.

The udpated file is attached.

[cham]

Hi @jdbuchanan71 

Thank you so much for your help. Appreciate it 🙂

[cham]

Hi @jdbuchanan71 

I have added a resposes field that now you can filter it. Please check the updated file.

[Ashish_Mathur]

Hi,

The value of N for more than 20 drivers is the same.  Perhaps this is the reason for the TopN not working.  Considering more then 20 drivers have the same value of N i.e. 100, what result do you expect to see?