Forum Discussion
Dynamic Rank Measure
Anonymous
Thank you for sharing the extended data set. I didn't really like the -1 either and with the extended data set it doesn't work because there are many companies not represented on a given quote and each of them was returning BLANK which is treated as a 0 in the ranking. This is fine if you do DESC but because we needed ASC it was counting them so the first company in Quote 1 that got a ranking was getting like 24.
What we needed it to filter the list of companies that are getting ranked to just those with Rate so here we go.
Ranking = IF ( ISFILTERED ( Quotes[Quote] ), RANKX ( FILTER ( ALL ( Quotes[Company] ), [Rate Total] ), [Rate Total],, ASC ), AVERAGEX ( VALUES ( Quotes[Quote] ), RANKX ( FILTER ( ALL ( Quotes[Company] ), [Rate Total] ), [Rate Total],, ASC ) ) )This portion of the measure (FILTER ( ALL ( Quotes[Company] ), [Rate Total]) returns all companies that have a rate in the quote context and if the Quote is not visable we still have quote context because we are iterating over the list of quotes then averaging the Ranking.
jdbuchanan71 Thanks a lot. Worked like a charm once I replaced All with AllSelected to make sure that the user selects on CompanyName was also taken into consideration by the measure.
jdbuchanan71 It worked like a charm. However, I feel like the "-1" can not be permanently implemented, due to the possibility of more companies rates being included in my data set. Can you think of a more flexible solution? To assist you, I have pasted a masked sample of my real data set below. Thank you so much for all the help so far.
| Quote | Company | Rate |
| 1 | C | 2815 |
| 1 | F | 3687 |
| 1 | G | 2654 |
| 1 | J | 3166 |
| 1 | K | 2754 |
| 1 | N | 1916 |
| 1 | O | 1755 |
| 1 | P | 3316 |
| 2 | C | 1672 |
| 2 | G | 1833 |
| 2 | M | 1678 |
| 2 | N | 2343 |
| 2 | O | 2191 |
| 2 | P | 3691 |
| 2 | Q | 2546 |
| 2 | U | 2096 |
| 2 | V | 1774 |
| 2 | W | 2182 |
| 2 | X | 2479 |
| 3 | B | 3522 |
| 3 | C | 2218 |
| 3 | E | 8585 |
| 3 | F | 4150 |
| 3 | G | 8020 |
| 3 | J | 3192 |
| 3 | K | 4507 |
| 3 | L | 3711 |
| 3 | M | 2263 |
| 3 | N | 2647 |
| 3 | O | 2367 |
| 3 | P | 1543 |
| 3 | U | 2074 |
| 3 | V | 1974 |
| 3 | W | 1741 |
| 4 | A | 1533 |
| 4 | B | 1323 |
| 4 | C | 870 |
| 4 | D | 1549 |
| 4 | E | 5188 |
| 4 | F | 1396 |
| 4 | G | 1367 |
| 4 | I | 1306 |
| 4 | J | 1210 |
| 4 | L | 1142 |
| 4 | M | 783 |
| 4 | R | 994 |
| 4 | S | 1520 |
| 4 | T | 773 |
| 4 | U | 1460 |
| 4 | X | 1141 |
| 5 | C | 2014 |
| 5 | D | 4474 |
| 5 | G | 2324 |
| 5 | K | 1910 |
| 5 | L | 1876 |
| 5 | N | 1108 |
| 5 | O | 1015 |
| 5 | V | 2039 |
| 6 | C | 912 |
| 6 | G | 1226 |
| 6 | M | 678 |
| 6 | N | 497 |
| 7 | C | 777 |
| 7 | F | 1104 |
| 7 | G | 1037 |
| 7 | R | 1234 |
| 7 | S | 764 |
| 7 | T | 966 |
| 7 | X | 1119 |
| 8 | G | 3459 |
| 8 | K | 989 |
| 8 | N | 765 |
| 9 | C | 6066 |
| 9 | D | 6047 |
| 9 | H | 7299 |
| 9 | L | 6595 |
| 9 | N | 3543 |
| 9 | O | 3090 |
| 10 | C | 2019 |
| 10 | G | 4549 |
| 10 | L | 3446 |
| 10 | N | 3732 |
| 10 | O | 3228 |
| 10 | P | 2926 |
| 11 | C | 1314 |
| 11 | G | 858 |
| 11 | L | 1674 |
| 11 | M | 1403 |
| 11 | N | 1354 |
| 11 | O | 1245 |
| 11 | U | 1464 |
| 11 | V | 2109 |
| 11 | W | 1783 |
| 12 | C | 1884 |
| 12 | V | 2497 |
| 13 | C | 2675 |
| 13 | D | 2044 |
| 13 | G | 1219 |
| 13 | L | 2191 |
| 13 | P | 1502 |
| 13 | U | 1374 |
| 13 | V | 1431 |
| 13 | W | 1393 |
Anonymous
Thank you for sharing the extended data set. I didn't really like the -1 either and with the extended data set it doesn't work because there are many companies not represented on a given quote and each of them was returning BLANK which is treated as a 0 in the ranking. This is fine if you do DESC but because we needed ASC it was counting them so the first company in Quote 1 that got a ranking was getting like 24.
What we needed it to filter the list of companies that are getting ranked to just those with Rate so here we go.
Ranking =
IF (
ISFILTERED ( Quotes[Quote] ),
RANKX ( FILTER ( ALL ( Quotes[Company] ), [Rate Total] ), [Rate Total],, ASC ),
AVERAGEX (
VALUES ( Quotes[Quote] ),
RANKX ( FILTER ( ALL ( Quotes[Company] ), [Rate Total] ), [Rate Total],, ASC )
)
)
This portion of the measure (FILTER ( ALL ( Quotes[Company] ), [Rate Total]) returns all companies that have a rate in the quote context and if the Quote is not visable we still have quote context because we are iterating over the list of quotes then averaging the Ranking.
jdbuchanan71 Thanks a lot. Worked like a charm once I replaced All with AllSelected to make sure that the user selects on CompanyName was also taken into consideration by the measure.