# VALUE when using AGGREGATE() against PowerPivot

Topics: Resolved
Jul 19, 2010 at 9:46 PM
I created a simple table (named Table1) for testing as follows: Letter Amount A 1 B 2 C 1 A 2 B 1 C 2 A 1 B 2 C 1 I was able to successfully create a calculated member called AB-SUM by defining it as follows: Calculation Name: [Table1].[Letter].[All].[AB-SUM] Formula: SUM({[Table1].[Letter].[All].[A],[Table1].[Letter].[All].[B]}) Now I would like to create a similar calculated member which would aggregate A & B for any measure. I tried the following: Calculation Name: [Table1].[Letter].[All].[AB-Agg] Formula: AGGREGATE({[Table1].[Letter].[All].[A],[Table1].[Letter].[All].[B]}) The member is added to the PivotTable, however, the PivotTable shows a #VALUE! error for the Sum of Amount measure. Is there any reason why the AGGREGATE function would not work with this add-in?
Coordinator
Jul 19, 2010 at 9:54 PM
Can you go to the MDX tab in OLAP PivotTable Extensions and capture the MDX query? Then, can you open SQL Server Management Studio (or get your OLAP admin to do this) and run the MDX query from an MDX window? That should show you the error messages when you mouse over the problem cells. Then we can troubleshoot further from there.
Jul 20, 2010 at 5:43 PM
I am working with a PowerPivot workbook so the only AS cube is the one that's embedded in the workbook. I can not access this embedded cube through SSMS. Any other ideas of how I could troubleshoot the calculation?
Coordinator
Jul 20, 2010 at 9:09 PM

Go to the Data tab and click the Connections button, then click Properties for the PowerPivot Data connection. What does your connection string say? Try making sure Cell Error Mode is set to TextValue and see if it displays the error message in the cell. For example:

Provider=MSOLAP.4;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Optimize Response=3;Cell Error Mode=TextValue

This page describes this Cell Error Mode property on the connection string:
http://powerpivot-info.com/post/411-changing-how-powerpivot-handles-dax-errors

To be honest, I'm not sure I know why you're getting an error. I was able to reproduce this error, and the error message with Cell Error Mode=TextValue was:

ERROR - CALCULATION ABORTED: MdxScript(Sandbox) (4, 48) The expression invokes an object that was defined by using an MDX calculated member or script. Such objects are not supported in DAX expressions.

My guess is that it's a bug in PowerPivot. You can try reporting it on Connect: http://connect.microsoft.com/SQLServer/feedback

Please post the link to the Connect bug report if you do.

Jul 21, 2010 at 12:25 AM
Edited Jul 21, 2010 at 12:25 AM

OK, so I had been using Excel 2010 Beta and there was no Cell Error Mode property in the connection string.  I then tested it out on another machine with Excel 2010 RTM and I got the same error message that you did. I'm not really sure how to report a bug on Connect, but I don't think there's much of a point in doing so anyway. The error message seems to clearly state that DAX (or for that matter the AS engine in PowerPivot) does not support MDX calculated members.

Coordinator
Aug 2, 2010 at 5:15 PM

You are correct that if it's not done using the Excel 2010 UI, it's not supported in PowerPivot v1. So using OLAP PivotTable Extensions (which uses the Excel API) won't be supported by Microsoft in PowerPivot v1. (It may work. But if it doesn't work, we're out of luck.) I verified this with Microsoft.

I was encouraged to report this bug anyway as they may choose to support this scenario in future PowerPivot versions. I've reported it here:
https://connect.microsoft.com/SQLServer/feedback/details/582492/powerpivot-excel-session-calculated-members-do-not-always-work

 

Marked as answer by furmangg on 6/9/2014 at 12:51 AM
Coordinator
Sep 22, 2010 at 2:35 PM
Edited Sep 22, 2010 at 2:37 PM

Microsoft responded to that Connect bug report with a very helpful hint. Try adding a positive solve_order to your calc, and they say it should work. For example, the following formula:

AGGREGATE({[Table1].[Letter].[All].[A],[Table1].[Letter].[All].[B]})
,Solve_Order=100

Will you try that and see if that trick works on your PowerPivot workbook?

Marked as answer by furmangg on 6/9/2014 at 12:51 AM
Sep 22, 2010 at 4:00 PM
Edited Sep 22, 2010 at 4:01 PM

I tried that but still got the same error message. 

Microsoft's reply on the Connect bug report states:

"but this may not work when the MDX Aggregate function is used in the definition of the Calculated Member."

The whole reason I am resorting to using MDX is to create a calculated member. The brilliant people at MS were the ones who decided that we don't need the ability to add a Calculated Item (dimension member) in an OLAP pivot table. What exactly were they thinking?

And using an explicit aggregation function like SUM does not work when you have measures that are ratios (or percentages). SUM will add the ratios -- what I want is the ratio of the SUMs, which is what AGGREGATE would do.

 

Coordinator
Sep 22, 2010 at 4:06 PM

It was worth a try. Hopefully the Solve_Order trick will help it succeed in some situations.

If you have a calculated measure which is a ratio, possibly the correct way to aggregate that is to aggregate the numerator, aggregate the denominator, then do the ratio. So that would require a low solve order for your calculated dimension member. You might try a Sum calculated dimension member with a low solve order and see if you like the results.

Dec 18, 2012 at 7:28 PM

Greg, I recently revisited this issue and it seems to have been fixed in PowerPivot 2012. That means it is now possible (using OLAP Pivot Table Extensions) to create a calculated (non-measure) member which aggregates multiple native members in the dimension even for ratio measures.

Marked as answer by furmangg on 6/9/2014 at 12:51 AM
Coordinator
Dec 20, 2012 at 3:59 PM

Agreed. They did make lots of improvements in PowerPivot 2012.

Unfortunately, when a cut down version of PowerPivot got baked into Excel 2013, they stopped supporting MDX calculations in PivotTables connected to PowerPivot:
http://olappivottableextend.codeplex.com/wikipage?title=Excel%202013

So don't rely on this too much, especially if you're planning on upgrading to Excel 2013 anytime soon.

Marked as answer by furmangg on 6/9/2014 at 12:50 AM
Coordinator
Jun 9 at 8:52 AM
Also consider this feature to see the error behind #VALUE! cells:
https://olappivottableextend.codeplex.com/wikipage?title=View%20Error%20Message&referringTitle=Home
Marked as answer by furmangg on 6/9/2014 at 12:52 AM