Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
|
Solved! Go to Solution.
Perhaps:
Column =
VAR __break = MAX([Rank])/3
RETURN
SWITCH(TRUE(),
[Rank] <= __break,15,
[Rank] > 2*__break,5,
10
)
Perhaps:
Column =
VAR __break = MAX([Rank])/3
RETURN
SWITCH(TRUE(),
[Rank] <= __break,15,
[Rank] > 2*__break,5,
10
)
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.
@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.
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 |
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |