percentilex.inc includes blanks

[itchyeyeballs]

Hi all,

I have an issue with the percentileX function.

PERCENTILEX.INC(TableA,[Measure1],.9)

The output is including all values from TableA even if measure1 returns blank for them. (I confirmed by forcing a row into my table for blank results then manually added up to run a check)

Does anyone know how I stop it doing this?

Thanks in advance

[v-ljerr-msft · Accepted solution]

Hi @itchyeyeballs,

In addition to OwenAuger's solution, the formula below should also work.

=
PERCENTILEX.INC (
    FILTER ( TableA, NOT ( ISBLANK ( [Measure1] ) ) ),
    [Measure1],
    .9
)

Regards

[itchyeyeballs]

Thanks guys, thats absolutly fantastic, I spent hours yesterday trying to figure that out, have now implimented and works perfectly.

Microsoft really need to document this "feature" it would be really easy for someone to misstakenly impliment. the naked percentilex function seems to be redundant as it stands.

[OwenAuger]

@itchyeyeballs

You're right, PERCENTILEX treats blanks as zeros, while AVERAGEX, MINX etc exclude blanks.

Not sure if this is by design.

One way to get around this would be something like this (using ADDCOLUMNS to avoid repeated calculation of [Measure1]):

=
PERCENTILEX.INC (
    FILTER (
        ADDCOLUMNS ( TableA, "MeasureCol", [Measure1] ),
        NOT ( ISBLANK ( [MeasureCol] ) )
    ),
    [MeasureCol],
    .9
)