Find Min Max of Measure Value for each Index filtered by slicer

[brspencer90]

Have a set of data with multiple criteria involved. I want to aggregate (average) the filtered data by index (ID) and find min / max of the filtered, aggregate data - and also find the rank of a separate but related value within that data table.

For example : 

Measures : 

ID = DISTINCT(SELECTCOLUMNS(df_query,"ID",[ID]))

Value = AVERAGEX(df_query,df_query[Value])

ValueToRank = AVERAGEX(df_query_other,df_query_other[Value])

df_query_other and df_query are not related though they have the same data. The idea is that I want the value from df_query_other to be compared to a list of anonymised and full data set, filtered by the same slicers (other than ID). df_query_other is filtered by a slicer equivalent to ID = 380

I created a visualisation table which does EXACTLY what I want to do when I plot the Measure Value and the ID column.

Is there away for me to pull data directly from a similar table? From this table I'd like to supply the reference "ID to Pull", and return the value of the column specified. I want to be able to get a ranking column as well, but I think that should be the easy part.

What is the value (TimeAddAfterPass) and rank of that value when compared to similar like values?

I'd prefer to keep this in code if at all possible, as I have to make these measures for a lot of features. The data originally comes from a SQL server database.

TIA. Have been struggling all week to find a solution to this.

[Greg_Deckler · Accepted solution]

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

[brspencer90]

Thanks Greg. This definitely worked. I also used this table to sort out additional problems I was having. Thanks so much :-).