Forum Discussion
Two stage RankX
Hi All,
I have a RankX working well but now want a second RankX based on the results of the first. I use the results as my y-axis in a scatter chart.
This table shows how my RankX works and I've hidden some rows to show what I want to achieve. For each change of programme as seen in the Programme Index column I want the minimum value from the RankX of Index column.
Here's the measure for my RankX of Index:
| Programme | Project | Milestones | Index | RankX of Index | Programme Index |
| Programme 1 | Project 1 | 01/08/2020 | 1 | 1 | 1 |
| Programme 1 | Project 3 | 03/08/2020 | 3 | 2 | 1 |
| Programme 2 | Project 4 | 04/08/2020 | 4 | 3 | 3 |
| Programme 2 | Project 6 | 06/08/2020 | 6 | 4 | 3 |
| Programme 3 | Project 7 | 07/08/2020 | 7 | 5 | 5 |
| Programme 3 | Project 8 | 08/08/2020 | 8 | 6 | 5 |
| Programme 3 | Project 9 | 09/08/2020 | 9 | 7 | 5 |
Hi Bravebart
I build a table like yours to have a test.
Build measures to achieve your goal.
RankX of Index = RANKX(ALLSELECTED(Milestones),CALCULATE(sum('Milestones'[Index])),,ASC)Programme Index = var _t = ADDCOLUMNS(ALLSELECTED(Milestones),"Rank",[RankX of Index]) return MINX(FILTER(_t,[Programme] = MAX([Programme])),[Rank])Result:
Default
Filter Programme1,2,3:
You can download the pbix file from this link: Two stage RankX
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Bravebart This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.Little different in your case with RANKX but same concept.
Hi Bravebart
I build a table like yours to have a test.
Build measures to achieve your goal.
RankX of Index = RANKX(ALLSELECTED(Milestones),CALCULATE(sum('Milestones'[Index])),,ASC)Programme Index = var _t = ADDCOLUMNS(ALLSELECTED(Milestones),"Rank",[RankX of Index]) return MINX(FILTER(_t,[Programme] = MAX([Programme])),[Rank])Result:
Default
Filter Programme1,2,3:
You can download the pbix file from this link: Two stage RankX
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Rico,
Thank you very much for helping with this. What you provided is exactly what I need. Well done.
Bravebart
