locked
Linest - Linear Regression in PowerPivot RRS feed

  • Question

  • Hi,

    Is it possible to you use Excel Functions within powerpivot, perhaps via ExcelMDX?  I want to use the LINEST function (Linear Regression).

    Thanks,

    Kevin

    Friday, August 31, 2012 12:58 PM

Answers

All replies

  • Hi Kevin,

    You can use ExcelMDX functionality within the MDX Pivot Table call to the PowerPivot engine as outlined in the blog entry I wrote below, which gives an example of Median and IRR Excel function calls:

    http://javierguillen.wordpress.com/2011/09/13/quartile-percentile-and-median-in-powerpivot-dax/




    Javier Guillen
    http://javierguillen.wordpress.com/


    • Edited by Javier GuillenEditor Friday, August 31, 2012 2:56 PM
    • Marked as answer by kza2 Friday, August 31, 2012 6:55 PM
    Friday, August 31, 2012 2:55 PM
    Answerer
  • Thanks Javier.  So I would need to package that assembly with my workbook?  Is that right?

    kevin

    Friday, August 31, 2012 3:38 PM
  • No, you dont need to package it.

    As PowerPivot inherits SSAS functionality, all you have to do is install the OLAP Pivot Table extensions add-in (from codeplex) which allows you to add custom MDX calculations to the query that is being sent to the PowerPivot engine (this engine understands DAX as well as MDX).

    MDX then gives you the ability to access Excel functionality within the query itself, by issuing a call as "Excel![function]".  The add-in is available here:  http://olappivottableextend.codeplex.com/ 




    Javier Guillen
    http://javierguillen.wordpress.com/



    Friday, August 31, 2012 5:21 PM
    Answerer
  • Excellent!!!  Is it possible to access Add-ins as well (FinCad)?

    Thanks,

    Kevin

    Friday, August 31, 2012 5:28 PM
  • I dont believe so.  Only Excel and MDX functionality.




    Javier Guillen
    http://javierguillen.wordpress.com/

    • Marked as answer by kza2 Friday, August 31, 2012 6:55 PM
    • Unmarked as answer by kza2 Friday, August 31, 2012 6:55 PM
    Friday, August 31, 2012 6:51 PM
    Answerer
  • Javier,

    Thanks for your help and guidance.

    Kevin

    Friday, August 31, 2012 6:56 PM
  • Javier - Alas, this technique (using OLAP Pivot Table extensions) no longer works in Excel 2013, so the statistical functions are no longer accessible via Excel! mechanism.   Is there any other way to do this for standalone workbooks (i.e. without access to an SSAS server)?
    Wednesday, November 27, 2013 1:20 PM
  • Kevin,

    I'm wondering if you can post the code showing how you were able to get LinEst to work. 

    I've tried the following code in the OLAP PivotTable Calculations tab, and I get the following Error: ERROR - CALCULATION ABORTED: Query(1,51) The 'LinEst' user-defined function returns an array, which is not supported. 

    Excel!Linest( SetToArray ( [Query].[POST INT].Members, [Measures].[Sum of AMOUNT] ) )

    I'm using Excel 2010, with the most recent version of PowerPivot. 

    Any suggestions?

    Ryan


    • Edited by Attspa Monday, December 16, 2013 8:56 PM
    Monday, December 16, 2013 8:55 PM