Dynamic Rank Measure

[Anonymous]

I'm trying to create a measure that re-calculates the rank in the power pivot table everytime the user deselects a value or changes the group by row labels. For example, per table below, there are 3 companies rates per quote id with their respective rank in each quote.

Quote	Company	Rate	Rank
1	A	500	1
1	B	600	2
1	C	700	3
2	A	700	3
2	B	500	1
2	C	600	2

Is there a measure that can recalulcate the rank of each company's rate per quote id if the user deselects company B so that power pivot table would look like this:

Quote	Company	Rate	Rank
1	A	500	1
1	C	700	2
2	A	700	2
2	C	600	1

Also, can the same measure also recalculate the rank by averaging the rank of each quote id if the user removed quote id from the row labels for the power pivot table so that power pivot table would look like this:

Company	Rank
A	1.5
C	1.5

Any help would be appreciated.

[jdbuchanan71 · Accepted solution]

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

Hello @Anonymous 

I was able to mirror your results with these two measures.

Rate Total = SUM ( Quotes[Rate] )

Ranking = 
IF (
    ISINSCOPE ( Quotes[Quote] ),
    RANKX ( ALLSELECTED ( Quotes[Company] ), [Rate Total],, ASC ),
    AVERAGEX (
        VALUES ( Quotes[Quote] ),
        ( RANKX ( ALLSELECTED ( Quotes[Company] ), [Rate Total],, ASC ) )
    )
)

My sample file is attached for you to look at.

[Anonymous]

@jdbuchanan71 This is superb. Thanks a lot. Is there an equivalent solution if this needs to be done using PowerPivot Excel?

[jdbuchanan71]

@Anonymous ,

I believe the measure should work in Excel as well, it's just DAX code.

[Anonymous]

@jdbuchanan71 Unfortunately, when I type this out in the new measure window, I get the following error:

"Failed to resolve name 'ISINSCOPE'. It is not a valid table, variable, or function name."

[jdbuchanan71]

Ahh, sorry about that.  Try it with ISFILTERED.

Ranking = 
IF (
    ISFILTERED ( Quotes[Quote] ),
    RANKX ( ALLSELECTED ( Quotes[Company] ), [Rate Total],, ASC ),
    AVERAGEX (
        VALUES ( Quotes[Quote] ),
        ( RANKX ( ALLSELECTED ( Quotes[Company] ), [Rate Total],, ASC ) )
    )
)

 That works for me in Excel

[Anonymous]

@jdbuchanan71 Very close to being perfect. I noticed in my data that sometimes there is a possibility that one of the quotes may not have a Company C rate but instead a Company D. For example:

Quote	Company	Rate
1	A	500
1	B	600
1	C	700
2	A	700
2	B	500
2	D	600

Unfortunately, the measure doesn't work. It looks like it is ranking from the distinct list of company names:

Quote	Company	Rate Total	Ranking	Correct Rank
1	A	500	2	1
1	B	600	3	2
1	C	700	4	3
2	A	700	4	3
2	B	500	2	1
2	D	600	3	2

[jdbuchanan71]

@Anonymous 

I think we are getting caught by the "blank" company on a quote where each company doesn't exist being ranked #1 but because we don't want to see that we can just shift the rank down.

Ranking:=IF (
    ISFILTERED ( Quotes[Quote] ),
    RANKX ( ALLSELECTED( Quotes[Company] ), [Rate Total],, ASC )-1,
    AVERAGEX (
        VALUES ( Quotes[Quote] ),
        ( RANKX ( ALLSELECTED( Quotes[Company] ), [Rate Total],, ASC ) )-1
    )
)

This gave me correct results across the sample but give it a test on your side.

[Anonymous]

@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

[jdbuchanan71 · Accepted solution]

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

[Anonymous · Accepted solution]

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