cancel
Showing results for
Did you mean:
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

Proud to be a Datanaut!

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
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.