How to ignore blank values in percentile calculation?

DAX DAX Calculations
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