Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Average and Percentage

Hi All,

 

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

 

Thank you in advance for the support!

 

Regards,

Salman 

Need Help
  • littlemojopuppy's avatar
    littlemojopuppy
    5 years ago

    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! ğŸ™‚

     

5 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        littlemojopuppy
        Icon for Community Champion rankCommunity Champion
        5 years ago

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