Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DLROLLINGS
Helper I
Helper I

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
v-eqin-msft
Community Support
Community Support

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

amitchandak
Super User
Super User

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

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

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.