topic Re: sum top values with rankx without duplicates in DAX Commands and Tips

sum top values with rankx without duplicates

Anonymous — Mon, 15 Nov 2021 12:01:54 GMT

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.

Re: sum top values with rankx without duplicates

amitchandak — Mon, 15 Nov 2021 12:45:22 GMT

Anonymous ,
Assume this a measure - total, create a new measures

total 1 = [total] +rand()

and

calculate([total],TOPN(10,allselected(table[name]),s[total 1],DESC))

Re: sum top values with rankx without duplicates

smpa01 — Mon, 15 Nov 2021 17:41:27 GMT

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

Re: sum top values with rankx without duplicates

Jihwan_Kim — Mon, 15 Nov 2021 19:02:02 GMT

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

)

RETURN

IF ( HASONEVALUE ( 'Table'[name] ), AVERAGEX ( top10table, [Total:] ) )

Re: sum top values with rankx without duplicates

Anonymous — Tue, 16 Nov 2021 09:55:23 GMT

Thanks smpa01 , worked like a charm 🙂

Re: sum top values with rankx without duplicates

Anonymous — Tue, 16 Nov 2021 09:58:37 GMT

Thanks Jihwan_Kim your solution worked too

Re: sum top values with rankx without duplicates

Anonymous — Tue, 16 Nov 2021 10:00:24 GMT

thanks amitchandak , I've tested your code but unfortunately didn't work correctly for different examples. You can check smpa01 and Jihwan_Kim answers