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