Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
Hi @itchyeyeballs,
In addition to OwenAuger's solution, the formula below should also work.
= PERCENTILEX.INC ( FILTER ( TableA, NOT ( ISBLANK ( [Measure1] ) ) ), [Measure1], .9 )
Regards
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.
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 )
User | Count |
---|---|
93 | |
83 | |
77 | |
73 | |
66 |
User | Count |
---|---|
115 | |
104 | |
93 | |
64 | |
61 |