Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
@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
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)
@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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |