How to get multiple values in CUBEVALUE

[macfuller]

I am able to download a portfolio and load to the data model, and I'd like to get summaries of the accounts into existing portions of a spreadsheet instead of having a separate pivot table. But I can't find a "simple" way of combining the accounts into a single result without using extra CUBEMEMBER functions.

The primary CUBEMEMBER function is in C2
=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Sum of Mkt Values with Accruals]", "Account Totals")

I can get the value of a single account using CUBEMEMBER and CUBEVALUE functions
e.g.
C7 =CUBEMEMBER("ThisWorkbookDataModel","[Holdings].[Account].&[IRA]")
and
B7 =CUBEVALUE("ThisWorkbookDataModel",$C$2,$C$7) provides the total value of the account.

So I'd like to add the IRA, stock (.&[Stock]), and muni (.&[Muni]) accounts together without having to do the CUBEMEMBER and CUBEVALUE syntax for each account, and then adding them together in yet another cell. I've tried various syntax options such as enclosing the values in {}, but nothing seems to work.
e.g. =CUBEMEMBER("ThisWorkbookDataModel",{"[Holdings].[Account].&[IRA]", "[Holdings].[Account].&[Stock]","[Holdings].[Account].&[Muni]"}, "All Accounts") returns #VALUE

Excel real estate is cheap so it's not a huge deal, but it seems like there should be a cleaner way to collapse 8 cells down to 2.

 

[Matt Allington]

CUBEVALUE and CUBEMEMBER, as their names suggest, refer to single values and members. But there are some things you can do.

Firstly, you don’t need to separate the member into a different cell. You can refer to the cubemember inside the cubevalue formula.

=CUBEVALUE("ThisWorkbookDataModel","[Holdings].[Account].&[IRA]",<your measure>)

Secondly, if there are some logical sub totals that you use often, you can add these roll ups into the tables in the datamodel.

Illustratively…

Acct, subTotal
A,A to C
B,A to C
C,A to C
D,D to F
E,D to F
F,D to F

By hard coding the subtotals this way in your model, yo can easily refer to them when needed by referring to that column in your formulas.

 

[macfuller]

Ah, thank you. Let Power Query or DAX do the work by adding the extra columns to group the accounts!

 

[Matt Allington]

That's it.