Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have a example dataset that look like this.
Item ID | Item Group | Cost | Revenue |
ITEM001 | A | 1 | 2 |
ITEM002 | B | 2 | 5 |
ITEM001 | A | 1 | 3 |
ITEM003 | A | 1 | 3 |
ITEM002 | B | 2 | 3 |
ITEM004 | B | 3 | 4 |
And I created measures to turn it into the table visulisation as below.
Display Revenue = CALCULATE(SUM('Sales'[Revenue])) Display Cost = CALCULATE(SUM('Sales'[Cost])) Display Margin = [Display Revenue] - [Display Cost]
Item ID | Item Group | Cost | Revenue | Margin |
ITEM001 | A | 2 | 5 | 3 |
ITEM002 | B | 4 | 8 | 4 |
ITEM003 | A | 1 | 3 | 2 |
ITEM004 | B | 3 | 4 | 1 |
Then I created a measure to do the ranking from the highest margin to the lowest.
Ranking = RANKX(ALL('Sales'[Item ID]), [Display Margin],,,Skip)
It works fine (as the table below) if I don't apply any filter.
Item ID | Item Group | Cost | Revenue | Margin | Ranking |
ITEM001 | A | 2 | 5 | 3 | 2 |
ITEM002 | B | 4 | 8 | 4 | 1 |
ITEM003 | A | 1 | 3 | 2 | 3 |
ITEM004 | B | 3 | 4 | 1 | 4 |
However, when I tried to add an Item Group slicer, the ranking breaks up in a strange way. It probably won't show up with the sample dataset but it has a huge impact on the real dataset.
The expected result should be something like the table below when applying Item Group A filter.
Item ID | Item Group | Cost | Revenue | Margin | Ranking |
ITEM001 | A | 2 | 5 | 3 | 1 |
ITEM003 | A | 1 | 3 | 2 | 2 |
How can I fix this? Thanks.
Solved! Go to Solution.
@Penn your ranking expression should be as below
Ranking = RANKX(ALL('Sales'[Item Group],'Sales'[Item ID]), [Display Margin],,,Skip)
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.
@Penn your ranking expression should be as below
Ranking = RANKX(ALL('Sales'[Item Group],'Sales'[Item ID]), [Display Margin],,,Skip)
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.
Hi there,
For some reason this meansure doesn't work for me, there is still a big jump in the rankings from 327 to 1127. It might because I have more columns in my table. Will look into this today. Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |