Error: CUBEVALUE Function returns #NUM!

[migueljoa]

Summary:

I'm using excel 2010 with powerpivot add-in.


I have constructured a powerpivot with calculated measures.


The pivot table is in a worksheet and I can see all the values that I need.


I would like to extract specific data from this pivot to make a more complex report.


I'm able to extract must of the data via CubeValue function, but there's one particular point in the pivot that when using CubeValue it returns [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NUM]#NUM [/URL] !


Alternatives that work... kindof:


I can use "=" and reference the specific point in the pivot table that I need, but I want to maintain consistency and keep all my cubevalue formulas the same and only change a member expression that filters the data.


When I convert the pivot into formulas, the cell that gives me the error is there and is a value, but the cubevalue function contains a cubeset functions that specifies members inside the set (this is manually specifying each member in "quotes"), and I don't like this solution because the set might change in the future.




Why am I getting this [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NUM]#NUM [/URL] ! error?

 

[migueljoa]

Here are more details:

This is a subtotal for DOP and USD currency from the original Pivot Table.

Below the results of trying to extract this data:
The direct link works without a problem
Convert pivot to formula works but it uses fixed values from the pivot
The Cube Value function for the USD subtotal works
And finally, the Cube Value Function for the DOP subtotal results in number error