Help Calculate/Percentile is calculating blank values instead of ignoring blanks

[Jkaelin]

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

[Greg_Deckler]

Add to your filter clause and exclude blank values?

[Jkaelin]

@Greg_Deckler

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.  

[Sean · Accepted solution]

@Jkaelin

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

[Jkaelin]

@Sean

Your solution worked!  Thank you very much.  I did a lot of research to no avail.  I appreciate the help you provide here.