Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
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.
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.
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.
@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 ,
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))
thanks @amitchandak , I've tested your code but unfortunately didn't work correctly for different examples. You can check @smpa01 and @Jihwan_Kim answers
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |