cancel
Showing results for
Search instead for
Did you mean:
Jkaelin Member

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

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 1 ACCEPTED SOLUTION

Accepted Solutions Super User

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

@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 4 REPLIES 4 Super User

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

Add to your filter clause and exclude blank values?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Jkaelin Member

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

@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. Super User

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

@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 Highlighted
Jkaelin Member

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

@Sean

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