Forum Discussion

itchyeyeballs's avatar
itchyeyeballs
Icon for Impactful Individual rankImpactful Individual
9 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
    9 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
9 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
    9 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.