Forum Discussion

Sajid02's avatar
Sajid02
Icon for Helper I rankHelper I
3 years ago

How to get ranking

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 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Sajid02
      Icon for Helper I rankHelper I
      3 years ago

      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 

       



  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    3 years ago
    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's avatar
      Sajid02
      Icon for Helper I rankHelper I
      3 years ago

      MH 

      Hi , 

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

      Snip