Top 20 Ranking

[C_Lee]

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]))
var tmp=ADDCOLUMNS(summry,"RNK",RANKX(summry,[Sum],,DESC,Dense))
return
MAXX(FILTER(tmp,[Player]=SELECTEDVALUE(Results[Player])),[RNK])

Source: Solved: Ranking a measure - Microsoft Power BI Community

Cheers,

Chris

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[C_Lee]

Hi @Ashish_Mathur 

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

Thank you for your response

[Ashish_Mathur]

Write this measure and drag it to the visual

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

Sort this measure in descending order.

[C_Lee]

@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?

[Ashish_Mathur · Accepted solution]

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.

[C_Lee]

@Ashish_Mathur legend, thank you so much. 

[Ashish_Mathur]

You are welcome.