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:

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.

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.

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

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?

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.

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.

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.

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.