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.
Good morning,
I have a measure that calculates the bottom quintile of a column, then calculates another column based on the filter. It works well except it is not ignoring the blank/null values in the P/FCF column below.
My measure: Bottom Quintile:=CALCULATE(Average(Table1[Return]),FILTER(Table1,Table1[P/FCF]<=PERCENTILE.EXC(Table1[P/FCF],0.2)))
The measure returns 8.98 (which includes the blank values in the bottom quintile) vs. the measure returning ~12.1 (which would exclude the blank values in the bottom quintile). Any advice/tips? Thank you! Kindly - James
Solved! Go to Solution.
You can include this either inside your FILTER like this...
Bottom Quintile =
CALCULATE (
AVERAGE ( Table1[Return] ),
FILTER (
Table1,
Table1[P/FCF] <= PERCENTILE.EXC ( Table1[P/FCF], 0.2 )
&& Table1[P/FCF] <> BLANK ()
)
)
Or
Bottom Quintile = CALCULATE ( AVERAGE ( Table1[Return] ), Table1[P/FCF] <> BLANK (), FILTER ( Table1, Table1[P/FCF] <= PERCENTILE.EXC ( Table1[P/FCF], 0.2 ) ) )
Either of those should give you 12.11
Add to your filter clause and exclude blank values?
I'm not following your response? What should I add to the filter in order to exclude blank values from the calculate function?
Thank you.
You can include this either inside your FILTER like this...
Bottom Quintile =
CALCULATE (
AVERAGE ( Table1[Return] ),
FILTER (
Table1,
Table1[P/FCF] <= PERCENTILE.EXC ( Table1[P/FCF], 0.2 )
&& Table1[P/FCF] <> BLANK ()
)
)
Or
Bottom Quintile = CALCULATE ( AVERAGE ( Table1[Return] ), Table1[P/FCF] <> BLANK (), FILTER ( Table1, Table1[P/FCF] <= PERCENTILE.EXC ( Table1[P/FCF], 0.2 ) ) )
Either of those should give you 12.11
Your solution worked! Thank you very much. I did a lot of research to no avail. I appreciate the help you provide here.
User | Count |
---|---|
77 | |
75 | |
67 | |
65 | |
47 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
65 |