Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Happy Monday everyone!
I would like to know how to write a measure to remove the top 20% of data from a given column and then use the Average function for the remaining value. There will be blank cells that I want to exclude.
See Example below: There are total of 22 rows, 2 of which has no value (blank). I want to remove the top 20% from the 20 rows with values, in this case, 50 and 49, then calculate the average for the remaining rows which should be 16.83 for the remaining 18 rows.
Project | Value |
A | 50 |
B | 49 |
C | 48 |
R | 40 |
P | 35 |
Q | 32 |
O | 30 |
T | 22 |
N | 20 |
M | 16 |
D | 15 |
E | 10 |
G | 6 |
S | 6 |
F | 5 |
I | 5 |
K | 5 |
H | 4 |
J | 2 |
L | 2 |
U | |
V |
Hi @punksterz626 ,
Here are the steps you can follow:
1. Create calculated column.
the top 20% =
COUNTROWS('Table') * 0.2
ranx =
var _count=COUNTX(FILTER(ALL('Table'),'Table'[Value]=BLANK()),[Project])
return
IF(
[Value]=BLANK(),BLANK(),RANKX(ALL('Table'),'Table'[Value],,DESC,Skip)+_count)
2. Create measure.
Avg =
AVERAGEX(FILTER(ALL('Table'),'Table'[ranx]>[the top 20%]),[Value])
Flag =
IF(
MAX('Table'[ranx])>MAX('Table'[the top 20%]),1,0)
3. Place [Flag]in Filters, set is=1, apply filter.
4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
That you sir I will review the suggested measures and report back.
Try this measure:
Average Out 20% =
VAR _2Top =
TOPN ( 2, FILTER ( 'Table', [Value] <> BLANK () ), 'Table'[Value], DESC )
VAR _Not_Blank =
FILTER ( 'Table', [Value] <> BLANK () )
VAR _Ex =
EXCEPT ( _Not_Blank, _2Top )
RETURN
AVERAGEX ( _Ex, [Value] )
output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hello,
Is it possible to further develop this measure to remove top 20% and bottom 20% and then do average on the rest of values?
what does this VAR means? It looks like top 2 number? What if my dataset is larger? The one provided is just a sample for simplicity.
VAR _2Top = TOPN ( 2, FILTER ( 'Table', [Value] <> BLANK () ), 'Table'[Value], DESC )
If you want to remove top 20% then in your main question you need to remove 4 top items (20%*20), but you mentioned 2 top items (can you please clear this)
for calculating the top 20% number, try this:
Average Out 20% =
Var _20PerNo =
ROUND( COUNTROWS( FILTER ( 'Table', [Value] <> BLANK () ))*0.2 , 0 )
VAR _2Top =
TOPN ( _20PerNo, FILTER ( 'Table', [Value] <> BLANK () ), 'Table'[Value], DESC )
VAR _Not_Blank =
FILTER ( 'Table', [Value] <> BLANK () )
VAR _Ex =
EXCEPT ( _Not_Blank, _2Top )
RETURN
AVERAGEX ( _Ex, [Value] )
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
The measure works; however, while playing with the formula I realized there are two additional columns that can alter this formula which i forgot to include. Although i can put a filter on the visual to achieve the final result, I was wondering if it is possible to include it in the measure.
AB_Date =Blank
Status =COMP
The result should be 13.43 (after removing top 20% on value column)
Project | Value | AB_Date | Status |
A | 50 | COMP | |
B | 49 | COMP | |
C | 48 | COMP | |
R | 40 | COMP | |
P | 35 | COMP | |
Q | 32 | COMP | |
O | 30 | COMP | |
T | 22 | COMP | |
N | 20 | COMP | |
M | 16 | COMP | |
D | 15 | COMP | |
E | 10 | COMP | |
G | 6 | COMP | |
S | 6 | COMP | |
F | 5 | COMP | |
I | 5 | COMP | |
K | 5 | COMP | |
H | 4 | COMP | |
J | 2 | COMP | |
L | 2 | COMP | |
U | 1/21/2022 | NOTCOM | |
V | 1 | 1/23/2022 | OUT |
Apparently, my math is just as bad as my DAX. You are correct, should be top 4. I will review the suggested measures and report back. Thank you so very much!
User | Count |
---|---|
87 | |
83 | |
67 | |
61 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
84 | |
70 |