cancel
Showing results for
Did you mean:
Frequent Visitor

## SUM Rank

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

4 REPLIES 4
Microsoft

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 )``````

Best Regards,

Eyelyn Qin

Super User IV

@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])

Proud to be a Super User!

Frequent Visitor

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.
Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!