cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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 )

9.15.3.1.png

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

Super User IV
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])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

HI Amit

Hopefully the following clears things up

 

AreaRank By DebtRank By Debt AccsRank By Avg. IncreaseRank By No of Acc Change
16264165
26363245
34952323
45960437
55456528

 

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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors