Answered by:
Linest  Linear Regression in PowerPivot
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

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/quartilepercentileandmedianinpowerpivotdax/
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 PMAnswerer
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/quartilepercentileandmedianinpowerpivotdax/
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 PMAnswerer 
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 addin (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 addin is available here: http://olappivottableextend.codeplex.com/
Javier Guillen
http://javierguillen.wordpress.com/
 Edited by Javier GuillenEditor Friday, August 31, 2012 5:25 PM
Friday, August 31, 2012 5:21 PMAnswerer 
Excellent!!! Is it possible to access Addins 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/Friday, August 31, 2012 6:51 PMAnswerer 
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' userdefined 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