Add category column with top10%, high20%... information in certain context

[IcingOnTheCake]

Dear Experts,

thanks for reading.

I have a table with some informational columns and a price column.

I would like to add one more column (category) with the information to what range (top 10%, high 20%, medium 30%, low40%) this priceamount belongs to based on a certain context. (By subsidiary etc.. context will be selected in slicers)

This result, the category column, should be available in a slicer.

For example:

Slicer with: top/high/medium/low

Company   Name               Quoteno    Price    Category

Sweden      Customer1       12566         100        low

Sweden      Customer2       12567         4000      high

Belgium      Customer6       1z665         100        low

...

Thank you for your help

[v-deddai1-msft · Accepted solution]

Hi @IcingOnTheCake ,

You could create the following two calculated column:

RANK = RANKX('Table','Table'[Price],,ASC,Dense) 

Category = var a = 'Table'[RANK]/MAX('Table'[RANK]) return SWITCH(TRUE(),a<=0.1,"top",a>0.1&&a<=0.3,"high",a>0.3&&a<=0.6,"medium",a>0.6&&a<=1,"low")

If this post help, please consider accept it as the solution to help others find it more  quickly.

Best Regard,

Dedmon Dai

[vanessafvg]

you can create a column with a switch statement

ie

SWITCH(
       TRUE(),
      [price]>100,"high″,
      [price]<100,"low″
            )

[IcingOnTheCake]

thank you @vanessafvg ! But the "100" needs to be variable. I dont know the ranges. it's by formula.

the price/row belongs to the top category, if the price is one of the top10% prices (in the table)

the price/row belongs to the high category, if the price is one of the prices that is in the range from top 11 to 30%

the price/row belongs to the medium category, if the price is one of the prices that is in the range from top 31% to 60% 

and 

the price/row belongs to the low category, if the price is one of the prices that is in the range from 61% to 100% 

sorry for the explanation. But it's hard to explain...

Thank you

[vanessafvg]

hi @IcingOnTheCake 

you will need to create a ranking first 

https://www.youtube.com/watch?v=z2qzJVeYhTY&t=195s

then use that ranking to assign the value.