CUBEVALUE – get directly values from Power Pivot data model

If you want to get data from Power Pivot data model, you don´t have too many options to do it. The most direct way is to use the Pivot Table, but often it doesn´t suit your demands. In this situations you can use the CUBEVALUE function.

In this example we will use this sales data sample. 

We want to get the total revenue for Mondays (and we already have the calculated measure Total revenue).

Lets go to sheet (in the same file where the data model is) and write this function:

• =CUBEVALUE(“ThisWorkbookDataModel”;”[Total revenue]”;”[Weekday].[Friday]]”)

Explanation:

• Cubevalue is a name of function
• “ThisWorkbookDataModel” is a name of connection. If you are not sure, check it in Data / Connections. 
• “[Total revenue]” is a name of measure, that has been used in Data Model (it is not working for common summary of column – you have to create the classical explicit measure). 
• “[Weekday].[Friday]]” because we don´t want all revenue – we only want Fridays. 

The arguments of this function can be taken from cell – just keep in mind that you need to connect it all together by CONCATENATE or ampersand… 

You can downlad the sample file with result from here