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.
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.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |