SUM Rank

[DLROLLINGS]

Hi all, 

I have applied some rankings to 4 criteria as measures with rankings from 1 to 70. I am looking at suming the ranking measures to give me an overall ranking score.

Any ideas on how to do this? I was hoping to simply sum(rank by debt,rank by increase) etc. 

Thanks

[v-eqin-msft]

Hi @DLROLLINGS ,

According to my understand, you want to sum all rank measures and then rank ,right?

You could use the following formula:

Create a column for sum of all rank:

sumRank =
[Rank by Debt] + [Rank by Debt Accs] + [Rank by Avg.Increase] + [Rank by No of Acc Change]

finalRank =
RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[sumRank] ) ),, DESC )

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[amitchandak]

@DLROLLINGS , not very clear .Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

You can try like

sumx(values(Table[debt]),[Ranking])

[DLROLLINGS]

HI Amit

Hopefully the following clears things up

Area	Rank By Debt	Rank By Debt Accs	Rank By Avg. Increase	Rank By No of Acc Change
1	62	64	1	65
2	63	63	2	45
3	49	52	3	23
4	59	60	4	37
5	54	56	5	28

So the above have been calculated using the following Rank by Debt = RANKX(ALL(Data[Local Authority Type]),[Current Debt],,DESC)

The current debt calculation is amended for Average Increase, Debt accounts and change in debt accounts. The data behind these is a weekly debt balance every Sunday using Max/Min based on a date slicer. 

I would like to add a column on the end that gives me an "Overall Ranking". Ideally, I would like the ranking to be something like the above 1-70 which would mean something like  = RANKX(ALL(Data[Local Authority Type]),[SUM OF ALL RANKINGS],,DESC) However I would just settle for being able to sum the ranking scores and sort the order. 

[Greg_Deckler]

@DLROLLINGS 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])
SumScoreMeasure = SUMX( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])

etc.