Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

2 ACCEPTED SOLUTIONS
Super User

@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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Click here and Go to My LinkedIn Page

6 REPLIES 6
Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Click here and Go to My LinkedIn Page

Anonymous
Not applicable

Thanks @Jihwan_Kim your solution worked too

Super User

@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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

Thanks @smpa01 , worked like a charm 🙂

Super User

@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
Not applicable

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors