Forum Discussion
sum top values with rankx without duplicates
I've been searching for a solution to my problem but I can't find it :
I want to get the average of the first top 10 total measure.
I created a rank measure first but I get duplicates rank values :
rank total = RANKX(ALL(Table[name]),[total],,DESC,Dense)
I've tried also TOPN function but I get wrong values because of the duplicates values.
Here's an a sample of first 10 rows :
| name | total | rank total | desired rank total | Desired output |
| a | 96% | 1 | 1 | 90.6% |
| b | 95% | 2 | 2 | 90.6% |
| c | 94% | 3 | 3 | 90.6% |
| d | 92% | 4 | 4 | 90.6% |
| e | 91% | 5 | 5 | 90.6% |
| f | 91% | 5 | 6 | 90.6% |
| g | 90% | 7 | 7 | 90.6% |
| h | 87% | 8 | 8 | 90.6% |
| i | 85% | 9 | 9 | 90.6% |
| j | 85% | 9 | 10 | 90.6% |
The desired output is :
Top 10 = 90.6 %
Thanks.
Anonymous you can achieve the end goal with following two measueres
Ranking = VAR _1 = RANKX ( ALL ( 'Table'[name] ), CALCULATE ( MAX ( 'Table'[total] ) ) + DIVIDE ( CALCULATE ( UNICODE ( MAX ( 'Table'[name] ) ) ), 1000000 ), , DESC ) RETURN IF ( _1 < 11, _1 ) Top10Average = VAR _1 = CALCULATE ( AVERAGEX ( FILTER ( ADDCOLUMNS ( 'Table', "X", RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[total] ) ) + DIVIDE ( CALCULATE ( UNICODE ( MAX ( 'Table'[name] ) ) ), 1000000 ), , DESC ) ), [X] < 11 ), [total] ), ALL ( 'Table'[name] ) ) RETURN IF ( [Ranking] <> BLANK (), _1 )pbix is attached
Hi,
Please check the below picture and the attached pbix file.
I added one more condition to calculate the ranking, that is the alphabet order.
I multiply it by 0.001. And then add to the [Total]. -> This is what I used as an expression inside the Ranking function and TOPN function.
Total: =IF ( HASONEVALUE ( 'Table'[name] ), SUM ( 'Table'[total] ) )Desired rank: =IF (HASONEVALUE ( 'Table'[name] ),RANKX (ALL ( 'Table' ),[Total:]+ 0.001* CALCULATE (RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[name] ) ),, DESC )),,DESC))Desired output: =VAR top10table =TOPN (10,ALL ( 'Table' ),[Total:]+ 0.001* CALCULATE (RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[name] ) ),, DESC )), DESC)RETURNIF ( HASONEVALUE ( 'Table'[name] ), AVERAGEX ( top10table, [Total:] ) )
6 Replies
- amitchandak
Super User
Anonymous ,
Assume this a measure - total, create a new measurestotal 1 = [total] +rand()
and
calculate([total],TOPN(10,allselected(table[name]),s[total 1],DESC))thanks amitchandak , I've tested your code but unfortunately didn't work correctly for different examples. You can check smpa01 and Jihwan_Kim answers
smpa01Community Champion
Anonymous you can achieve the end goal with following two measueres
Ranking = VAR _1 = RANKX ( ALL ( 'Table'[name] ), CALCULATE ( MAX ( 'Table'[total] ) ) + DIVIDE ( CALCULATE ( UNICODE ( MAX ( 'Table'[name] ) ) ), 1000000 ), , DESC ) RETURN IF ( _1 < 11, _1 ) Top10Average = VAR _1 = CALCULATE ( AVERAGEX ( FILTER ( ADDCOLUMNS ( 'Table', "X", RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[total] ) ) + DIVIDE ( CALCULATE ( UNICODE ( MAX ( 'Table'[name] ) ) ), 1000000 ), , DESC ) ), [X] < 11 ), [total] ), ALL ( 'Table'[name] ) ) RETURN IF ( [Ranking] <> BLANK (), _1 )pbix is attached
Thanks smpa01 , worked like a charm 🙂
- Jihwan_Kim
Hi,
Please check the below picture and the attached pbix file.
I added one more condition to calculate the ranking, that is the alphabet order.
I multiply it by 0.001. And then add to the [Total]. -> This is what I used as an expression inside the Ranking function and TOPN function.
Total: =IF ( HASONEVALUE ( 'Table'[name] ), SUM ( 'Table'[total] ) )Desired rank: =IF (HASONEVALUE ( 'Table'[name] ),RANKX (ALL ( 'Table' ),[Total:]+ 0.001* CALCULATE (RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[name] ) ),, DESC )),,DESC))Desired output: =VAR top10table =TOPN (10,ALL ( 'Table' ),[Total:]+ 0.001* CALCULATE (RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[name] ) ),, DESC )), DESC)RETURNIF ( HASONEVALUE ( 'Table'[name] ), AVERAGEX ( top10table, [Total:] ) )Thanks Jihwan_Kim your solution worked too
