cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Top 20 Ranking

Hi,

Can someone please help me out with this one, I have searched the community and tried a number of the solutions tht have been provided but I have unfortunatley I haven't had any luck.

I want to create a rank measure that give me the top 20 ratios if that ratio is higher than 2.0. I was unable to create another measure based on the existing measure. Therefore I figured I need to add the calculation in the ration measure to my new rank measure and have had no luck..Here is basic ratio measure....

Ratio = SUM('Table'[Field])/SUM('Table'[Field])

I tried this out which I thought might give me what I want but didn't work out

```SumPointsRank =
Var summry=SUMMARIZE(ALLSELECTED(Results),[Player],"Sum",SUM(Results[Points]))
return
MAXX(FILTER(tmp,[Player]=SELECTEDVALUE(Results[Player])),[RNK])```

Cheers,

Chris

1 ACCEPTED SOLUTION
Super User

Hi,

You may download my PBI file from here.  You will see the Top 3 Pincodes by ratio in descending order.  The Top 3 are obtained by a DAX measure.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
7 REPLIES 7
Super User

Hi,

Share some data and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

The data I am dealing with looks like

 Money In Money Out Postcode 14000 19000 3001 13000 11000 3002 22000 13000 3003 17000 16000 3004 11900 11000 3005 15672 90000 3006

Expected Result

• New Ratio column ranked highest to smallest
 Money In Money Out Postcode Ratio 15672 9000 3006 1.74 22000 13000 3003 1.69 13000 11000 3002 1.18 11900 11000 3005 1.08 11900 11000 3005 1.06 15672 90000 3006 0.73

Super User

Write this measure and drag it to the visual

Measure = divide(sum(Data[Money In]),sum(Money out))

Sort this measure in descending order.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

@Ashish_Mathur yeah thank I have that calc sorted but how do I go about sorting them dynamically and then get a list of the top 20 in dax?

Super User

Hi,

You may download my PBI file from here.  You will see the Top 3 Pincodes by ratio in descending order.  The Top 3 are obtained by a DAX measure.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

@Ashish_Mathur legend, thank you so much.

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.