Create calculated column to make special case at the bottom while other ranking by themselves

[Anonymous]

I have tried below to create calculated column in order to rank end of my contract=15(A14) and Personal reasons=16(A15) for these 2 special case, while others(A1,...,A13,A16) should be ranked among themselves according to their current % of workers. The final target is to make the table visuals putting  A14 and A15 at the bottom(RANK=15,16, while others ranked among ranking 1-14(even % is lower than personal reasons/end of my contract). I think i must use calculated column, as i would to finally use the conditional formatting for the field value by making measure based on the ranking i am asking.  Note that % of workers include both % of past and current %.

Rank(current) = IF(Q25_TOP5[Current/last]="Current Survey",IF(Q25_TOP5[Answer text]="End of my contract",15, IF(Q25_TOP5[Answer text]="Personal reasons", 16, RANKX(FILTER(ALL(Q25_TOP5[Merged]),Q25_TOP5[Answer text]<>"End of my contract" && Q25_TOP5[Answer text]<>"Peasonal reasons"), [% of workers],,DESC))))

 sample data:

Q25_TOP5

A123	A1	20%	9/9/2022	current
A123	A2	30%	9/9/2022	current
A123	A3	10%	9/9/2022	current
A123	A4	5%	9/9/2022	current
A123	A5	6%	9/9/2022	current
A123	A6	52%	9/9/2022	current
A123	A7	1%	9/9/2022	current
A123	A8	3%	9/9/2022	current
A123	Other	52%	9/9/2022	current
A123	No	41%	9/9/2022	current

A121	A1	20%	3/9/2022	last
A121	A2	30%	3/9/2022	last
A121	A3	10%	3/9/2022	last
A121	A4	5%	3/9/2022	last
A121	A5	6%	3/9/2022	last
A121	A6	52%	3/9/2022	last
A121	A7	1%	3/9/2022	last
A121	A8	3%	3/9/2022	last
A121	Other	52%	3/9/2022	last
A121	No	41%	3/9/2022	last

A119	A1	20%	1/9/2022	-
A119	A2	30%	1/9/2022	-
A119	A3	10%	1/9/2022	-
A119	A4	5%	1/9/2022	-
A119	A5	6%	1/9/2022	-
A119	A6	52%	1/9/2022	-
A119	A7	1%	1/9/2022	-
A119	A8	3%	1/9/2022	-
A119	Other	52%	1/9/2022	-
A119	No	41%	1/9/2022	-

[Anonymous]

Hi @Anonymous ,

According to your sample, I couldn't find "Current Survey"/"End of my contract"/"Personal reasons" and so on. Please share a sample file with me and show me a screenshot with the result you want. This will make it easier to me to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.