Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 val...
  • smpa01's avatar
    smpa01
    4 years ago

    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's avatar
    Jihwan_Kim
    4 years ago

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

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

Thanks Jihwan_Kim your solution worked too