locked
Calculate trimmed mean in DAX mean RRS feed

  • Question

  • I am very inexperienced when it comes to statistical methods but I need to use one for a model I am working on in PowerPivot. 

    The method I need is TRIMMED MEAN as I need to exclude outliers from an average in the same way as described here:

    http://stats.stackexchange.com/questions/16198/what-do-you-call-an-average-that-does-not-include-outliers

    I have searched and it appears that Excel has a built-in function for this but DAX does not. What is the best way to do this in PowerPivot?

    Thanks. 
    Wednesday, November 4, 2015 11:56 AM

Answers

  • Hi Maracles,

    According to your description, you need to calculated the average and exclude outliers in you PowerPivot data model, right?

    Based on my research, there is no such a functionally to work around this requirement directly in current version of PowerPivot. You can calculate median in PowerPivot, here are some useful links for you reference.
    http://blog.gbrueckl.at/2013/04/calculating-median-in-powerpivot-using-dax/
    https://javierguillen.wordpress.com/2011/09/13/calculating-median-in-powerpivot-dax/

    If this is not what you want, you can submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope it is resolved in the next release of service pack or product. Your feedback enables Microsoft to make software and services the best that they can be, Microsoft might consider to add this feature in the following release after official confirmation.

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Monday, November 16, 2015 9:06 AM
    • Marked as answer by Charlie Liao Wednesday, December 2, 2015 8:51 AM
    Thursday, November 5, 2015 6:59 AM
  • If you know in advance what the thresholds are for outliers, you can use a CALCULATE() to exclude values past the threshold.

    If you can derive a measure that calculates the threshold for you, then you can include this in a FILTER() Within the CALCULATE() to remove outliers. If you can describe the logic you need, I can help with the measures.

    GNet Group BI Consultant

    • Proposed as answer by Charlie Liao Monday, November 16, 2015 9:06 AM
    • Marked as answer by Charlie Liao Wednesday, December 2, 2015 8:51 AM
    Thursday, November 5, 2015 5:10 PM

All replies

  • Hi Maracles,

    According to your description, you need to calculated the average and exclude outliers in you PowerPivot data model, right?

    Based on my research, there is no such a functionally to work around this requirement directly in current version of PowerPivot. You can calculate median in PowerPivot, here are some useful links for you reference.
    http://blog.gbrueckl.at/2013/04/calculating-median-in-powerpivot-using-dax/
    https://javierguillen.wordpress.com/2011/09/13/calculating-median-in-powerpivot-dax/

    If this is not what you want, you can submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope it is resolved in the next release of service pack or product. Your feedback enables Microsoft to make software and services the best that they can be, Microsoft might consider to add this feature in the following release after official confirmation.

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Monday, November 16, 2015 9:06 AM
    • Marked as answer by Charlie Liao Wednesday, December 2, 2015 8:51 AM
    Thursday, November 5, 2015 6:59 AM
  • If you know in advance what the thresholds are for outliers, you can use a CALCULATE() to exclude values past the threshold.

    If you can derive a measure that calculates the threshold for you, then you can include this in a FILTER() Within the CALCULATE() to remove outliers. If you can describe the logic you need, I can help with the measures.

    GNet Group BI Consultant

    • Proposed as answer by Charlie Liao Monday, November 16, 2015 9:06 AM
    • Marked as answer by Charlie Liao Wednesday, December 2, 2015 8:51 AM
    Thursday, November 5, 2015 5:10 PM