Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

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

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , 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])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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. 
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    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