Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Ranking

Hi there,

 

This is my very first post here in the community, so I hope I'm doing it in the right way 🙂

 

I need some help with creating a dax formula for the table "Commodity" , that shows the rank for "Physical Commodity" based on the "Contracted Quantity", ignoring the impact of the "Date", like below:

"Contracted Quantity" is a measure, and "Date" and "Physical Commodity" are part of different tables.

Can you give me some suggestions? 

 

Thanks

 

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

     

    Contract Qty: = 
SUM( sales[Contracted Quantity] )

     

    Ranking by physical commodity = 
RANK (
    SKIP,
    ALL ( 'Physical Commodity'[Physical Commodity] ),
    ORDERBY ( [Contract Qty:], DESC )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Jihwan_Kim ,

       

      Thanks for the suggestion, but it does not work 😞

      If I add "Date" to the the table it's messing up with the rank.

       

      Thanks,

      Georgiana

       

       

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    hi,  Anonymous 

     

    adjust your table name
    try below one
    result =
    var a = SUMMARIZECOLUMNS('physical commodity'[physical commodity],"cont commodity",[contracted commodity])

    var b = SELECTCOLUMNS(a,"rankcomparing",[cont commodity])
    return 

    rankx(b,[rankcomparing],[Contracted Quantity])

     

    'physical commodity' -- is your table name so adjust it .

    [Contracted Quantity]  -- is your measue

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Dangar332,

       

      [contracted commodity] from variable "a", I believe should be "contracted quantity", right?

      In the form with "contracted quantity" the formula does not show anything 😞

      Thanks

       

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    hi, Anonymous 

    try below measure  

    rank1 =
    RANK(SKIP,ALL('rank'),ORDERBY('rank'[contracted quantity],ASC))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Dangar332  Jihwan_Kim  thanks for trying.

       

      I manage to solve it with Calculate and All functions.

       

      Have a nice day!