Forum Discussion

Taro_Gulat's avatar
Taro_Gulat
Regular Visitor
1 year ago
Solved

Ranking issue

Dear all, 

 

I am having difficulty in writing DAX for the below scenario:

 

Need to define a ranking for each product based on amount. if A is top product then divide amount for A with amount of second top product else divide amount of A with the top product. 

 

Is it possible this calculation using measure?

 

Thanks

Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago

    What do you want all the other values to do?

     

     

    Divide by =
    var a = [sum amount] //Current row
    var s = tOPN(2,All('Table'),[sum amount] ,DESC) //Top 2 rows with highest amount
    var cur = SELECTEDVALUE('Table'[Product])
    RETURN
    SWITCH(
        TRUE()
        ,cur = "A" && a = maxx(s, [sum amount]) --if the max is equal to current row and A
        ,Sum('Table'[Amount]) / MINX(s, [sum amount]) --Divide by the second highest
        ,cur = "A" && a <> maxx(s, [sum amount]) --if a and not highest
        ,sum('Table'[Amount]) / maxx(s,[sum amount]) // divide by highest
        ,BLANK() --else blank
    )

     

7 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    1 year ago

    Hi Taro_Gulat 
    Create a measure with your Sum Amount.

    sum amount = sum('Table'[Amount])


    Then create a measure to return the top 2 results.
    If the current row matches the max then divide by the 2nd row otherwise divide by the top row.

    Divide by =
    var a = [sum amount] //Current row
    var s = tOPN(2,All('Table'),[sum amount] ,DESC) //Top 2 rows with highest amount
    RETURN
    If(
        a = maxx(s, [sum amount]) --if the max is equal to current row
        ,Sum('Table'[Amount]) / MINX(s, [sum amount]) --Divide by the second highest
        ,sum('Table'[Amount]) / maxx(s,[sum amount]) //else divide by highest
    )
    • Taro_Gulat's avatar
      Taro_Gulat
      Regular Visitor
      1 year ago

      Hi, thanks for the feedback. I need to keep Product A as reference if A is number 1 in rank then divide A with number 2 in rank else divide A with number 1 in rank. 

      • SamWiseOwl's avatar
        SamWiseOwl
        Super User
        1 year ago

        What do you want all the other values to do?

         

         

        Divide by =
        var a = [sum amount] //Current row
        var s = tOPN(2,All('Table'),[sum amount] ,DESC) //Top 2 rows with highest amount
        var cur = SELECTEDVALUE('Table'[Product])
        RETURN
        SWITCH(
            TRUE()
            ,cur = "A" && a = maxx(s, [sum amount]) --if the max is equal to current row and A
            ,Sum('Table'[Amount]) / MINX(s, [sum amount]) --Divide by the second highest
            ,cur = "A" && a <> maxx(s, [sum amount]) --if a and not highest
            ,sum('Table'[Amount]) / maxx(s,[sum amount]) // divide by highest
            ,BLANK() --else blank
        )

         

  • Laxmanjatoth's avatar
    Laxmanjatoth
    Resolver I
    1 year ago

    hello try this 

    Product_Ranking =
    VAR TopProductAmount =
    CALCULATE(
    MAX(Table[Amount]),
    ALLEXCEPT(Table, Table[Product])
    )
    VAR SecondTopProductAmount =
    CALCULATE(
    MAX(Table[Amount]),
    FILTER(
    Table,
    Table[Amount] < TopProductAmount
    )
    )
    VAR CurrentProductAmount = SUM(Table[Amount])
    RETURN
    IF(
    CurrentProductAmount = TopProductAmount,
    DIVIDE(CurrentProductAmount, SecondTopProductAmount, 0),
    DIVIDE(CurrentProductAmount, TopProductAmount, 0)
    )

  • Laxmanjatoth's avatar
    Laxmanjatoth
    Resolver I
    1 year ago

    Product_Ranking =
    VAR TopProductAmount =
    CALCULATE(
    MAX(Table[Amount]),
    ALLEXCEPT(Table, Table[Product])
    )
    VAR SecondTopProductAmount =
    CALCULATE(
    MAX(Table[Amount]),
    FILTER(
    Table,
    Table[Amount] < TopProductAmount
    )
    )
    VAR CurrentProductAmount = SUM(Table[Amount])
    RETURN
    IF(
    CurrentProductAmount = TopProductAmount,
    DIVIDE(CurrentProductAmount, SecondTopProductAmount, 0),
    DIVIDE(CurrentProductAmount, TopProductAmount, 0)
    )