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 amo...
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
    )

     

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
)