Forum Discussion

itchyeyeballs's avatar
itchyeyeballs
Icon for Impactful Individual rankImpactful Individual
10 years ago
Solved

percentilex.inc includes blanks

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 confir...
  • v-ljerr-msft's avatar
    v-ljerr-msft
    10 years ago

    Hi itchyeyeballs,

     

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

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

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

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's avatar
itchyeyeballs
Icon for Impactful Individual rankImpactful Individual
10 years ago

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.