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.
Hello together,
I'm struggling with creating rank for projects when project cell is empty.
Project | order | value | OrderRank |
Project 1 | 100 | $10.000 | 1 |
101 | $250.000 | 5 | |
Project 2 | 102 | $15.000 | 3 |
Project 1 | 103 | $12.000 | 2 |
Project 2 | 104 | $80.000 | 4 |
As you can see for order number 101 there is no Project reference.
Rank for order I created using:
OrderRank:=RANKX(ALL(fOrders[order]);[Selected TopN Value];;0)
This works great.
Adapting this to Project level PrjRank:=RANKX(ALL(fOrders[Project]);[Selected TopN Value];;0)
What I would like to have:
Project | value | Project Rank |
Project 1 | $22.000 | 1 |
Project 2 | $95.000 | 2 |
What I unfortunately get:
Project | value | Project Rank |
$250.000 | 1 | |
Project 1 | $22.000 | 2 |
Project 2 | $95.000 | 3 |
I have tried wrapping the all with a filter as well as wrapping the expresseion [Selected TopN Value] in a Calculate with filter but non of this worked.
I've also checked:
https://community.powerbi.com/t5/Desktop/Rankx-with-filter/m-p/63457#M26162
https://community.powerbi.com/t5/Desktop/Rankx-with-filter/m-p/8213#M1308
I haven't found a solution yet.
Maybe there is a BI hero out there able to help me.
Best regards.
Solved! Go to Solution.
Try this MEASURE
ProjectLevelRank = IF ( NOT ( SELECTEDVALUE ( fOrders[Project] ) = BLANK () ), RANKX ( FILTER ( ALLSELECTED ( fOrders[Project] ), fOrders[Project] <> BLANK () ), CALCULATE ( SUM ( fOrders[value] ) ), , ASC, DENSE ) )
Try this MEASURE
ProjectLevelRank = IF ( NOT ( SELECTEDVALUE ( fOrders[Project] ) = BLANK () ), RANKX ( FILTER ( ALLSELECTED ( fOrders[Project] ), fOrders[Project] <> BLANK () ), CALCULATE ( SUM ( fOrders[value] ) ), , ASC, DENSE ) )
Hello @Zubair_Muhammad,
Amazing! It works, I would never have figured this out by myself.
unfortunately SELECTEDVALUE isn't available to me, so I had to rephrase it with IF(HASONEVALUE(<column>);Values(<column))
Maybe I will find the time to understand this formula entirely.
Best regards.
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 |
---|---|
102 | |
101 | |
78 | |
70 | |
63 |
User | Count |
---|---|
140 | |
106 | |
100 | |
83 | |
73 |