Need Help with ranking within a group power bi

[Anonymous]

 I am trying to rank lanes within each equipment type based on amount charged. The result I am trying to see is ranks as 1,2,3  etc for each lane within various equipment types.

Can anyone help with this

 

[Anonymous · Accepted solution]

Hi @Anonymous，

You can create one measure as below if your expected result is same with the one in below screenshot:

Ranking = RANKX(ALL('Equipment'[LANES]),CALCULATE(SUM('Equipment'[AMOUNT_CHARGED])),,DESC)

Best Regards

Rena

[Greg_Deckler]

It would be very helpful to know the formula for [tot] measure as this could explain any weirdness you are seeing. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

@Greg_Deckler 

tot = sum([AMOUNT_CHARGED])

[JarroVGIT]

Hi @Anonymous 

Please have a look at my blog about ranking:

https://community.powerbi.com/t5/Community-Blog/Breaking-Ties-in-Rankings-with-RANKX-Using-Multiple-Columns/ba-p/918655

If you want more advanced ranking in your visual, I usually create more advanced measures like this:

RankMeasureInSpecificVisual =
VAR _tmpTable = SUMMARIZE(Table, Column1, Column2, "totColumn", [tot])
VAR _rankedTable = ADDCOLUMNS(_tmpTable, "Rank", RANKX(_tmpTable, [totColumn]))
RETURN
MAXX(FILTER(_rankedTable, [Column1] = SELECTEDVALUE(Table[Column1]) && [Column2] = SELECTEDVALUE(Table[Column2])), [Rank])

Let me know if this works for you 🙂

[Anonymous]

Hi @JarroVGIT 

I took a look at your solution and looks like the values under Name column in distinct in that case. Do you know if we can rank the values when we have different values in the second column for the same value in first column.

[amitchandak]

I think you do not need the third parameter. This best explained in articles given below.

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[Anonymous]

@amitchandak 

somehow it doesn't work...I tried the first link which explains ranking by subcategory but when i try to use equipment type field in earlier function it get greyed out throwing an error.

[amitchandak]

The one trying out should be created as a column, not as a measure. Those which has aggerated field are the one need to created as a measure