Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

help rank data

Hello Community,

 

I need to categorize a visual by the below: 

 

Black / platinum is the 5% of the entity with highest spend

Gold is top 20% to top 5%

Silver is top 50% to top 20%

Bronze is bottom 50%

 

Noting that Code and Name comes from a dimension table1 while Year and Month comes from tabl2, spend is a measure

can you help how to address this ? is there any other method than the Rank or topN 

 

Thank you

screenshot of exported data from Power BI

 

Need Help
  • Deku's avatar
    Deku
    1 year ago

    I forgot to unfilter the table, updated in the first post

5 Replies

  • Deku's avatar
    Deku
    Super User
    1 year ago
    medal =
var tbl = 
CALCUATETABLE(
   SUMMARIZE(
      tabl2,
      table1[Code],
      table1[Name],
      tabl2[YEAR],
      tabl2[Month]
   ),
   ALLSELECTED()
)
var platinum = PERCENTILEX.INC(tbl, [Spend], 0.95)
var gold =     PERCENTILEX.INC(tbl, [Spend], 0.8)
var silver =   PERCENTILEX.INC(tbl, [Spend], 0.5)
var val =      [Spend]
RETURN
SWITCH(
   true,
   val >= platinum, "Platinum",
   val >= gold,     "Gold",
   val >= silver,   "Silver",
   "Bronze"
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thank you for your input, I put the formula that you have provided but I only got Platinum and bronze values only

      • Deku's avatar
        Deku
        Super User
        1 year ago

        I forgot to unfilter the table, updated in the first post