cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jkaelin Member
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

Percentile.PNG

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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 Smiley Happy

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

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