How to get ranking

[Sajid02]

Hi Folks,

Need help on the ranking formula . As per the need :- 

1. We need to show ranking as per Product and price from higher to lower i.e higher value should be 1 and then goes on ....

2. We need to show all city in the same row of a particular product. For example:- Pls find the snips 

Case 1-

Case 2 -

For ranking i am trying to write DAX as 

1.

Ranking = IF('Table'[Product] = "Healthcare", RANKX('Table','Table'[Sales], ,DESC, Dense) , RANKX('Table','Table'[Sales], ,DESC, Dense))

2.

Ranking 1 =

RANKX (

    ALLSELECTED ( 'Table'[Product], 'Table'[City]),

    CALCULATE ( SUM ( 'Table'[Sales] ) ),

    ,

    DESC,

    DENSE

)

Both these codes are showing wrong output as :-

Kinldy help me to solve this 

[mh2587]

rank = RANKX( FILTER( data, AND( data[Product] = EARLIER(data[Product]), data[City] = EARLIER(data[City]))), data[Price], , Dense)
--Try this one hope it works 

[Sajid02]

@MH 

Hi , 

This is not working as passing more than 2 argument in filter function is not accepting 

Snip 

[Anonymous]

For case 1 could you try this please?

Ranking = VAR healthcare_products = FILTER('Table', 'Table'[Product] = "Healthcare") RETURN RANKX(healthcare_products, healthcare_products[Sales], , DESC, Dense)

let me know if this gives desired result, if so I should be able to do case 2 🙂 

[Sajid02]

Hi @Anonymous 

I tried this with a minor changes in the DAX but still showing the wrong ranking for Pharmacy in Pune . PLs find below snip