Formula Help

[Anonymous]

I have used a RANKX formula on a column named Clothes and now I want the top third of the data to get assigned a value of 15, the middle third of the data to be assigned 10, and the bottom third to be given a 5.  How would I do this?

Clothes	Rank
Pants	4
Hat	7
Shirts	9
Shorts	2
Dress	5
Skirts	8
Socks	6
Jacket	1
Shoes	3

[Greg_Deckler · Accepted solution]

Perhaps:

Column = 
  VAR __break = MAX([Rank])/3
RETURN
  SWITCH(TRUE(),
    [Rank] <= __break,15,
    [Rank] > 2*__break,5,
    10
  )
  

[Anonymous]

HI @Greg_Deckler 

I am trying to write this formula in my Power Bi Desktop and I am not sure how to do so.  I have never written a formula that looks like this before. 

[Greg_Deckler]

@Anonymous  - Right-click the table in the Fields area, New Column. Formula bar should appear, copy and paste the code.

[parry2k]

@Anonymous can you post what your expected output should be. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

I added another column in the table which is labed Risk Score.  This data is just a very small and simple sample data to what I am actually using.  As you can see in the Risk Score column, the top 3 got a Risk Score of 15, the middle 3 got a score of a 10, and the bottom three got a score of 5. I want me full data to have formula where it can stratify the data into thirds and assign it the proper risk score. 

Clothes	Rank	Risk Score
Pants	4	10
Hat	7	5
Shirts	9	5
Shorts	2	15
Dress	5	10
Skirts	8	5
Socks	6	10
Jacket	1	15
Shoes	3	15