Percentile group Category

[Varshi288]

Hello Group,

I have three Percetail columns like 30, 50 & 65th Percentiles for frame size. I also have another table with Catagory (Gold Silver & Bronze). I want to calculate attractiveness based on logic.

Attractiveness If 65th percentile > Gold --> Attractiveness = High
ELSE, If 30th Percentile > Bronze --> Attractiveness = Average
ELSE, --> Attractiveness = Low

Customers30th Percentile50th Percentile65th Percentile     

16	845 $	846 $	846 $
18	952 $	2,140 $	3,210 $
20	1,280 $	1,320 $	1,398 $
22	1,999 $	2,141 $	2,141 $
25	2,567 $	2,567 $	2,567 $
31	5,309 $	5,309 $	5,309 $

Frame	Frame Size	Bronze	Silver	Gold
5	56	118	118	118
6	63	141	151	177
7	71	131	149	173
8	80	217	257	297
9	90	263	300	321
10	100	332	410	457
11	112	421	436	502
13	132	524	650	724
16	160	1,015	1,172	1,364
18	180	1,174	1,490	1,725
20	200	1,925	2,305	2,567
22	225	2,678	2,991	3,282
25	250	3,267	3,515	3,964
28	280	3,526	5,217	6,350
31	315	5,496	6,967	8,529
35	355	12,932	15,680	18,182
40	400	26,602	31,782	38,655
45	450	45,338	52,131	59,338

Output.

[Varshi288 · Accepted solution]

Hi  @Anonymous 

Thanks  for the reply. Acutually the percentile values are measures and this achieved by using variables.

[Anonymous]

Hi @Varshi288 ,

Table 1.

Table 2.

Please create a relationship first.

If you want a calculated column.

Attractiveness = 
SWITCH(
    TRUE(),
   CALCULATE(MAX('Table 1'[65th Percentile]))>CALCULATE(MAX('Table 2'[Gold])),"High",
   CALCULATE(MAX('Table 1'[30th Percentile]))>CALCULATE(MAX('Table 2'[Bronze])),"Average",
    "Low")

Or a measure.

Measure = 
SWITCH(
    TRUE(),
    MAX('Table 1'[65th Percentile])>MAX('Table 2'[Gold]),"High",
    MAX('Table 1'[30th Percentile])>MAX('Table 2'[Bronze]),"Average",
    "Low")

Attach the PBIX file for reference. Hope it helps.

If this doesn't work for you or I misunderstand your needs, please consider sharing more details about it. And it would be great if there was a sample file without any sensitive information here.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar.
2. Expected output from sample data.
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data