Forum Discussion

rpinxt's avatar
rpinxt
Icon for Solution Sage rankSolution Sage
3 years ago
Solved

Can you make Rankx look at variable table

I have this variable: VAR rank_qty = RANKX(ALLSELECTED('Backorder Details'[Origin]),[BO Qty],,DESC)   Now the part " 'Backorder Details'[Origin]) " I want to make variable because I am using a fi...
  • rpinxt's avatar
    3 years ago

    Well I think I pulled it off with this code 😁:

    Rank Measure =
    VAR selected = SELECTEDVALUE(prmMeasure[Measure2])
    VAR selected2 = SELECTEDVALUE(prmBreakdown[Breakdown])

    VAR rank_org_qty = RANKX(ALLSELECTED('Backorder Details'[Origin]),[BO Qty],,DESC)
    VAR rank_org_usd = RANKX(ALLSELECTED('Backorder Details'[Origin]),[USD Amt],,DESC)

    VAR rank_pom_qty = RANKX(ALLSELECTED('Backorder Details'[POM]),[BO Qty],,DESC)
    VAR rank_pom_usd = RANKX(ALLSELECTED('Backorder Details'[POM]),[USD Amt],,DESC)

    VAR rank_pf_qty = RANKX(ALLSELECTED('Backorder Details'[PF Name]),[BO Qty],,DESC)
    VAR rank_pf_usd = RANKX(ALLSELECTED('Backorder Details'[PF Name]),[USD Amt],,DESC)

    RETURN

    SWITCH(TRUE(),
        selected = "BO Qty" && selected2 = "Origin", rank_org_qty,
        selected = "USD Amt" && selected2 = "Origin", rank_org_usd,
        selected = "BO Qty" && selected2 = "POM", rank_pom_qty,
        selected = "USD Amt" && selected2 = "POM", rank_pom_usd,
        selected = "BO Qty" && selected2 = "Prod.Fam. Name", rank_pf_qty,
        selected = "USD Amt" && selected2 = "Prod.Fam. Name", rank_pf_usd
    )

     

    But does anybody know how I can alter the code so it does not als rank the totals??

     

    *EDIT: I found out! 😁
    Did it in declaring the VARs :

    Rank Measure =
    VAR selected = SELECTEDVALUE(prmMeasure[Measure2])
    VAR selected2 = SELECTEDVALUE(prmBreakdown[Breakdown])

    VAR rank_org_qty = IF( HASONEVALUE('Backorder Details'[Origin]),RANKX(ALLSELECTED('Backorder Details'[Origin]),[BO Qty],,DESC))
    VAR rank_org_usd = IF( HASONEVALUE('Backorder Details'[Origin]),RANKX(ALLSELECTED('Backorder Details'[Origin]),[USD Amt],,DESC))

    VAR rank_pom_qty = IF( HASONEVALUE('Backorder Details'[POM]),RANKX(ALLSELECTED('Backorder Details'[POM]),[BO Qty],,DESC))
    VAR rank_pom_usd = IF( HASONEVALUE('Backorder Details'[POM]),RANKX(ALLSELECTED('Backorder Details'[POM]),[USD Amt],,DESC))

    VAR rank_pf_qty = IF( HASONEVALUE('Backorder Details'[PF Name]),RANKX(ALLSELECTED('Backorder Details'[PF Name]),[BO Qty],,DESC))
    VAR rank_pf_usd = IF( HASONEVALUE('Backorder Details'[PF Name]),RANKX(ALLSELECTED('Backorder Details'[PF Name]),[USD Amt],,DESC))

    RETURN

    SWITCH(TRUE(),
    selected = "BO Qty" && selected2 = "Origin", rank_org_qty,
    selected = "USD Amt" && selected2 = "Origin", rank_org_usd,
    selected = "BO Qty" && selected2 = "POM", rank_pom_qty,
    selected = "USD Amt" && selected2 = "POM", rank_pom_usd,
    selected = "BO Qty" && selected2 = "Prod.Fam. Name", rank_pf_qty,
    selected = "USD Amt" && selected2 = "Prod.Fam. Name", rank_pf_usd
    )

     

    Thanks to OwenAuger for putting me on the right track!