Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Formula Help

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
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Perhaps:

 

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

Perhaps:

 

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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. 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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 RankRisk Score
Pants410
Hat75
Shirts95
Shorts215
Dress510
Skirts85
Socks610
Jacket115
Shoes315

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.