Forum Discussion
Helper IIRANK on Multiple Measures with different Order
Hi there,
I have a scenario that each month contractors Quoting with more than 4 options with different criteria's, some criterias values are higher the better, some criterias value are lower the better. All the Criterias are calculated as a "Measure". So I need to Rank all the multiple measures together, considering ranking order, and for each month. Could you please kindly help to formulate this?, it will be very help ful.
| Month | Name | Option | Criteria 1 % (Higher the better) | Criteria 2 Number (Higher the better) | Criteria 3 % (Lower the better) | Criteria 4 Number (Lower the better) |
| Jan.21 | Contractor 1 | Option 1 | 90% | 6 | 90% | 6 |
| Jan.21 | Contractor 1 | Option 2 | 92% | 7 | 92% | 7 |
| Jan.21 | Contractor 1 | Option 3 | 93% | 6.5 | 93% | 6.5 |
| Jan.21 | Contractor 1 | Option 4 | 87% | 7 | 87% | 7 |
| Jan.21 | Contractor 2 | Option 1 | 87% | 7.5 | 87% | 7.5 |
| Jan.21 | Contractor 2 | Option 2 | 86% | 8 | 86% | 8 |
| Jan.21 | Contractor 2 | Option 3 | 97% | 8 | 97% | 8 |
| Jan.21 | Contractor 2 | Option 4 | 95% | 8.5 | 95% | 8.5 |
| Jan.21 | Contractor 3 | Option 1 | 75% | 9 | 75% | 9 |
| Jan.21 | Contractor 3 | Option 2 | 98% | 9.5 | 98% | 9.5 |
| Jan.21 | Contractor 3 | Option 3 | 97% | 7 | 97% | 7 |
| Jan.21 | Contractor 3 | Option 4 | 93% | 6 | 93% | 6 |
| Feb.21 | Contractor 1 | Option 1 | 90% | 6 | 90% | 6 |
| Feb.21 | Contractor 1 | Option 2 | 92% | 7 | 92% | 7 |
| Feb.21 | Contractor 1 | Option 3 | 93% | 6.5 | 93% | 6.5 |
| Feb.21 | Contractor 1 | Option 4 | 87% | 7 | 87% | 7 |
| Feb.21 | Contractor 2 | Option 1 | 87% | 7.5 | 87% | 7.5 |
| Feb.21 | Contractor 2 | Option 2 | 86% | 8 | 86% | 8 |
| Feb.21 | Contractor 2 | Option 3 | 97% | 8 | 97% | 8 |
| Feb.21 | Contractor 2 | Option 4 | 95% | 8.5 | 95% | 8.5 |
| Feb.21 | Contractor 3 | Option 1 | 75% | 9 | 75% | 9 |
| Feb.21 | Contractor 3 | Option 2 | 98% | 9.5 | 98% | 9.5 |
| Feb.21 | Contractor 3 | Option 3 | 97% | 7 | 97% | 7 |
| Feb.21 | Contractor 3 | Option 4 | 93% | 6 | 93% | 6 |
Hi,
this is what i get
using this 2 measures (RankTotal is not necessary but helps to understand)
RankTotal by Month =var currentmonth = filter('Table',month('Table'[Date]))var Rank1 = RANKX(currentmonth,'Table'[Criteria 1],,ASC)var Rank2 = RANKX(currentmonth,'Table'[Criteria 2],,ASC)var Rank3 = RANKX(currentmonth,'Table'[Criteria 3],,DESC)var Rank4 = RANKX(currentmonth,'Table'[Criteria 4],,desc)Var RankTotal = Rank1+Rank2+Rank3+Rank4returnRankTotalRankGlobal by Month =var currentmonth = filter('Table',month('Table'[Date]))var Rank1 = RANKX(currentmonth,'Table'[Criteria 1],,ASC)var Rank2 = RANKX(currentmonth,'Table'[Criteria 2],,ASC)var Rank3 = RANKX(currentmonth,'Table'[Criteria 3],,DESC)var Rank4 = RANKX(currentmonth,'Table'[Criteria 4],,desc)Var RankTotal = Rank1+Rank2+Rank3+Rank4var RankGlobal = RANKX(currentmonth,'Table'[RankTotal by Month],,Desc,Dense)returnRankGlobalIf this post is useful to help you to solve your issue consider giving the post a thumbs up
and accepting it as a solution !
7 Replies
- serpiva64
Solution Sage
Hi,
it's not clear to me why there are 4 records for each option on the same contractor as appears in your sample data.
Nevertheless i think that a calculeted column like this might solve your problem and obtain something like this:
This is the calculated column
RankTotal =
var Rank1 = RANKX('Table','Table'[Criteria 1],,DESC)var Rank2 = RANKX('Table','Table'[Criteria 2],,Desc)var Rank3 = RANKX('Table','Table'[Criteria 3],,ASC)var Rank4 = RANKX('Table','Table'[Criteria 4],,ASC)Var RankTotal = Rank1+Rank2+Rank3+Rank4returnRankTotalYou can also weight your criteria by multiplying for a fixed coefficient or maybe for a parameter if you need to change it frequently.If this post is useful to help you to solve your issue consider giving the post a thumbs up 👍 and accepting it as a solution ! Hi k_mathana ,
If you need group ranking, you can refer to the following measure.
measure = rankx(filter(allselected('table'),[Month] = selectedvalue([Month])),[Criteria ],,desc)
In your case, you have four Criterias. You will need to set the proportion for each Criteria. Otherwise it could return same rank if we just sum the rank values.
Best Regards,
Jay
