Two stage RankX

[Bravebart]

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:

Measure = RANKX(ALLSELECTED('Milestones'),'Milestones'[Index],CALCULATE(sum('Milestones'[Index])),DESC,Skip)

Any help appreciated.

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

[v-rzhou-msft · Accepted solution]

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. 

[Bravebart]

Hi Rico,

Thank you very much for helping with this. What you provided is exactly what I need. Well done.

Bravebart

[Greg_Deckler]

@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.