How to ignore blank values in percentile calculation?

DAX DAX Calculations
1

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)

blank
blank.PNG237×701 23.4 KB

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!

2

@nainiu440,

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

image
image.png930×511 15.5 KB

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.

I hope this is helpful.

  • Brian

eDNA Forum - Percentile.pbix (43.6 KB)

1 Like