Cube formulas without creating a Pivot table

[Andrea Staunsholm]

I played with the datamodel from:
http://chandoo.org/wp/2013/07/01/in...013-data-model-relationships/#comment-1456901

Creating tables and relationships between them. It also works in Excel 2016.
I can even create Cube Formulas on the Data model after I've inserted the pivot table.

Just for the fun of it, I tried to see if I could do Cube Formulas without creating the pivot table in advance. I can define Cube members, but it seems as if the measure part is playing tricks on me.

I can't get a Cube Value for Chocolates sold to Male customers.
With the Pivot created the formula looks like this (and works fine)
=CUBEVALUE("ThisWorkbookDataModel";"[Customer].[Gender].&[Male]";"[Product].[Category].&[Chocolates]";"[Measures].[Sum of Quantity]")

I've uploaded a file for you to look in, the problem cells are marked yellow.

Does anyone know how I can solve this, or am I asking the impossible?

 

[Chihiro]

Well... without measure being defined in data model you can't use it in CUBE functions.

If in data model, measure is defined using DAX like below image...



Then you'd be able to access it.


However, this method requires PowerPivot add-in. Without it, to access measure, you'd first need to create Pivot Table and using OLAP tool, convert it into formula.

 

[Andrea Staunsholm]

Thank you - I figured I'd need to define the sum first.