Average and Percentage

[Anonymous]

Hi All,

I need help in calculating "Average of bottom 95% values in a column". 

Thank you in advance for the support!

Regards,

Salman 

[littlemojopuppy · Accepted solution]

Hi @Anonymous.  Define "bottom".  I'm going to assume you mean the 95% of records with the lowest values, or discard those records with the highest values.

Here's what you do:

1. As a variable calculate the 95th percentile with the PERCENTILEX.INC function
2. Calculate the sum filtering the table for where the values < the variable calculated in step one.

Here's some code from something similar that I did...

VAR PercentileCutoff =
    PERCENTILEX.INC ( CalculateDaysToSubmission, [DaysToSubmission], .99 )
RETURN
    AVERAGEX (
        FILTER (
            CalculateDaysToSubmission,
            [DaysToSubmission] >= 0
                && [DaysToSubmission] <= PercentileCutoff
        ),
        [DaysToSubmission]
    )

Hope this help! 🙂

[Anonymous]

Hi @littlemojopuppy , thank you for a quick response. And you were right, I wanted to skip out highest values. However, the code you provided is not working for me, for some reason it gives error from "RETURN" till the end. I am not sure what I did wrong, hoping you can help:

VAR M_95% =
PERCENTILEX.INC ( 'Vendor SLA', [Networking Days], .95 )
RETURN
AVERAGEX (
FILTER (
'Vendor SLA',
[Networking Days] >= 0
&& [Networking Days] <= M_95%
),
[Networking Days]
)

I really do appreciate your support on this 🙂

Regards,

Salman

[littlemojopuppy]

Hi @Anonymous did you name the measure?  It doesn't look like there's a name

[Anonymous]

Hi @littlemojopuppy it worked perfectly! Thank you for the help.

[littlemojopuppy]

@Anonymous you'rew welcome!  Glad I could help!  🙂