Forum Discussion

Evelien's avatar
Evelien
Icon for Advocate I rankAdvocate I
8 years ago
Solved

Calculated column with rank based on value, plus dependency on 2 categories

Hi,

I am looking for the DAX formula to create a calculated column to show the rank by value, per month and per product.

The rank column should look like this:

 

MonthProductCompanyValueRank
1/01/2017AHello3001
1/01/2017ABla2502
1/01/2017AHey1003
1/02/2017BHello203
1/02/2017BBla402
1/02/2017BHey501
1/01/2017BBla2451

 

Many thanks for your help!

 

Kind regards,

Evelien

Need Help
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    Evelien

     

    Hi, try with this:

     

    Rank-CC =
RANKX (
    ALL ( Table1[Company] ),
    CALCULATE (
        SUM ( Table1[Value] ),
        ALLEXCEPT ( Table1, Table1[Month], Table1[Product], Table1[Company] )
    )
)

    Regards

     

    Victor

    Lima - Peru

3 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Evelien

     

    Hi, try with this:

     

    Rank-CC =
RANKX (
    ALL ( Table1[Company] ),
    CALCULATE (
        SUM ( Table1[Value] ),
        ALLEXCEPT ( Table1, Table1[Month], Table1[Product], Table1[Company] )
    )
)

    Regards

     

    Victor

    Lima - Peru

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Hi Evelien

     

    Try this calculated column

     

    RANK_ =
RANKX (
    FILTER (
        ALL ( TableName ),
        MONTH ( TableName[Month] ) = MONTH ( EARLIER ( TableName[Month] ) )
            && TableName[Product] = EARLIER ( TableName[Product] )
    ),
    TableName[Value],
    ,
    DESC,
    DENSE
)
    • Evelien's avatar
      Evelien
      Icon for Advocate I rankAdvocate I
      8 years ago

      Hi Zubair_Muhammad,

       

      Thanks for your help, but I do not get the correct rank when using the formula you suggested :-S