sum top values with rankx without duplicates

[Anonymous]

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.

[smpa01 · Accepted solution]

@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

[Jihwan_Kim · Accepted solution]

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:] ) )

[Anonymous]

Thanks @Jihwan_Kim your solution worked too

[smpa01 · Accepted solution]

@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

[Anonymous]

Thanks @smpa01 , worked like a charm 🙂

[amitchandak]

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

[Anonymous]

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