help rank data

[Elieekh17]

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

[Deku]

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"
)

[Elieekh17]

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

[Deku · Accepted solution]

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

[Elieekh17]

Thank you @Deku , it worked I adjusted the spend >0 and not is blank

[Elieekh17]

VAR tbl = CALCULATETABLE(

   SUMMARIZE(

      SITE,

      SITE[SRU_CODE],

      SITE[SRU_DESCL_EN]

   ),

   ALLSELECTED()

)

VAR platinum = PERCENTILEX.INC(tbl, [Spend per Nb Purchaser 12RM], 0.95)

VAR gold =     PERCENTILEX.INC(tbl, [Spend per Nb Purchaser 12RM], 0.7)

VAR silver =   PERCENTILEX.INC(tbl, [Spend per Nb Purchaser 12RM], 0.5)

VAR val = [Spend per Nb Purchaser 12RM]

RETURN IF(NOT(ISBLANK([Spend per Nb Purchaser 12RM])),

SWITCH(

   TRUE(),

   val >= platinum, "Platinum",

   val >= gold,     "Gold",

   val >= silver,   "Silver",

   "Bronze"

)

)

I couldn't add the 2 columns from TIME table and the result is only platinum and gold 😞