When do we use cube formulas?
Cube formulas should be used when you need more flexibility in your calculations beyond what PivotTables give you. After creating measures, the only way to give expression to the measures in your worksheet is to create PivotTables and drag those measures into the VALUE section of the PivotTable. With cube formulas, your measures do not always have to sit in PivotTables. We can get the measures directly in cells from the data model and use them in calculations in our worksheet.
As an example, if I want to call or see the Total Sales measure we calculated earlier in a cell, we can use CUBEVALUE to retrieve this measure directly from the cube or data model. I used the word “retrieve” because these measures are already calculated and stored by dimensions and hierarchies in the cube. When we use cube formulas, we are essentially using a combination of expressions to retrieve a specific measure or dimension we need from the data model.
We...