 # How to ignore blank values in percentile calculation?

Hello,

I have a quick question about how to ignore the blank values in percentile calculation. I made a very simple virtual table as showed below, where the column ‘Duration’ is a measure. I’d like to calculate the 75 percentile of this measure. So I used :
`75 Percentile of Duration = PERCENTILEX.INC(VALUES('Schedule Analytics'[ProjectName]),[Duration],0.75)`

There are several blanks in the virtual table, which is fine because the blank means there is no such Duration value for some projects. When I used the formula above to calculate the 75 percentile value, I think it treated blank as zero, so the result is not correct. I used the filters to exclude blank values but still got the same result. What else should I try to fix this?

thanks a lot!

This was interesting. I wasn’t sure how PERCENTILEX.INC handled blanks, so I created three test cases from your small sample data set below. Note: I also changed the percentile from 75% to 25%, since 75% is high enough to skip over all the blanks in the data set, and thus with all the non-blank durations equal to one, the filtered and nonfiltered results are the same, which is not a generalizable outcome - just a quirk of the particular sample data set provided. Dropping the figure down to 25% illustrates the difference in how the function handles the three following cases:

Case #1 - sample table is unfiltered, and blanks are left as blanks

Case #2 - sample table is filtered to exclude all rows where [Duration] is blank

Case #3 - sample table where blanks are replaced by zeros, unfiltered

If for purposes of your analysis, blanks are not considered values, then Case #2 is the correct result.

Here’s the measure I used for Case #2:

``````25 Percentile Duration Filtered =
VAR NoBlanksTable =
FILTER(
SELECTCOLUMNS('Schedule Analytics Blanks',
"Project", 'Schedule Analytics Blanks'[Project Name],
"Duration Elapsed", 'Schedule Analytics Blanks'[Duration]
),
[Duration Elapsed]<>BLANK()
)

RETURN
PERCENTILEX.INC (
NoBlanksTable,
[Duration Elapsed],
0.25
)
``````

Full solution file attached below.