Forum Discussion

k_mathana's avatar
k_mathana
Icon for Helper II rankHelper II
4 years ago
Solved

RANK on Multiple Measures with different Order

Hi there, I have a scenario that each month contractors Quoting with more than 4 options with different criteria's, some criterias values are higher the better, some criterias value are lower the be...
Need Help
  • serpiva64's avatar
    serpiva64
    4 years ago

    Hi,

    this is what i get

    using this 2 measures (RankTotal is not necessary but helps to understand)

    RankTotal by Month =
    var currentmonth = filter('Table',month('Table'[Date]))
    var Rank1 = RANKX(currentmonth,'Table'[Criteria 1],,ASC)
    var Rank2 = RANKX(currentmonth,'Table'[Criteria 2],,ASC)
    var Rank3 = RANKX(currentmonth,'Table'[Criteria 3],,DESC)
    var Rank4 = RANKX(currentmonth,'Table'[Criteria 4],,desc)
    Var RankTotal = Rank1+Rank2+Rank3+Rank4
    return
    RankTotal
     
     
    RankGlobal by Month =
    var currentmonth = filter('Table',month('Table'[Date]))
    var Rank1 = RANKX(currentmonth,'Table'[Criteria 1],,ASC)
    var Rank2 = RANKX(currentmonth,'Table'[Criteria 2],,ASC)
    var Rank3 = RANKX(currentmonth,'Table'[Criteria 3],,DESC)
    var Rank4 = RANKX(currentmonth,'Table'[Criteria 4],,desc)
    Var RankTotal = Rank1+Rank2+Rank3+Rank4
    var RankGlobal = RANKX(currentmonth,'Table'[RankTotal by Month],,Desc,Dense)
    return
    RankGlobal
     

    If this post is useful to help you to solve your issue consider giving the post a thumbs up 

     and accepting it as a solution !

serpiva64's avatar
serpiva64
Icon for Solution Sage rankSolution Sage
4 years ago

Hi, 

it's not clear to me why there are 4 records for each option on the same contractor as appears in your sample data.

Nevertheless i think that a calculeted column like this might solve your problem and obtain something like this:

This is the calculated column

 

RankTotal =

var Rank1 = RANKX('Table','Table'[Criteria 1],,DESC)
var Rank2 = RANKX('Table','Table'[Criteria 2],,Desc)
var Rank3 = RANKX('Table','Table'[Criteria 3],,ASC)
var Rank4 = RANKX('Table','Table'[Criteria 4],,ASC)
Var RankTotal = Rank1+Rank2+Rank3+Rank4
return
RankTotal
 
You can also weight your criteria by multiplying for a fixed coefficient or maybe for a parameter if you need to change it frequently.
 
If this post is useful to help you to solve your issue consider giving the post a thumbs up 👍 and accepting it as a solution !
 
k_mathana's avatar
k_mathana
Icon for Helper II rankHelper II
4 years ago

Hi serpiva64,

Thanks for given solution, but I need to rank within each month. so each month best contractot with best option will be choosen!

  • serpiva64's avatar
    serpiva64
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Sorry, 

    in your exemple there is only 1 contractor.

    Can you specify your sample data? 

    • k_mathana's avatar
      k_mathana
      Icon for Helper II rankHelper II
      4 years ago

      serpiva64 sorry in my exampe there were 3 contractors and each contractor has 4 options.

       

      Example

      • serpiva64's avatar
        serpiva64
        Icon for Solution Sage rankSolution Sage
        4 years ago

        Hi,

        this is what i get

        using this 2 measures (RankTotal is not necessary but helps to understand)

        RankTotal by Month =
        var currentmonth = filter('Table',month('Table'[Date]))
        var Rank1 = RANKX(currentmonth,'Table'[Criteria 1],,ASC)
        var Rank2 = RANKX(currentmonth,'Table'[Criteria 2],,ASC)
        var Rank3 = RANKX(currentmonth,'Table'[Criteria 3],,DESC)
        var Rank4 = RANKX(currentmonth,'Table'[Criteria 4],,desc)
        Var RankTotal = Rank1+Rank2+Rank3+Rank4
        return
        RankTotal
         
         
        RankGlobal by Month =
        var currentmonth = filter('Table',month('Table'[Date]))
        var Rank1 = RANKX(currentmonth,'Table'[Criteria 1],,ASC)
        var Rank2 = RANKX(currentmonth,'Table'[Criteria 2],,ASC)
        var Rank3 = RANKX(currentmonth,'Table'[Criteria 3],,DESC)
        var Rank4 = RANKX(currentmonth,'Table'[Criteria 4],,desc)
        Var RankTotal = Rank1+Rank2+Rank3+Rank4
        var RankGlobal = RANKX(currentmonth,'Table'[RankTotal by Month],,Desc,Dense)
        return
        RankGlobal
         

        If this post is useful to help you to solve your issue consider giving the post a thumbs up 

         and accepting it as a solution !