CUBE function, the magic function of Excel report (2)

Preliminary preparation for connecting to CUBE

Cube is a member of the function family, but its special feature is that its use depends on the created model. In order to let everyone focus on the Cube function, two files are specially prepared:

▪Simple Cube model built with PowerBI

▪Special Excel to connect any Cube model with one click

The two documents can be obtained by replying the keyword "CUBE" to the official account.

Simple model introduction:

The model contains a sales subject data, the core is an order table, the detailed data related to the salesperson, region, customer, and product related to the order are all in other tables, and they are associated with the order table according to key fields. This is the CUBE model for our practice.

Open the Powerbi file of this DEMO, then open the customized Excel file, click the "button" in sheet1, and Excel is successfully connected to the CUBE model.

Get the first CUBE function with a pivot table

The easiest way to learn the CUBE function is to observe and imitate, so we first create a pivot table, then convert the pivot table into a CUBE function, and then disassemble the CUBE function.

Step 1: Click the button to get the pivot table as shown in the figure

Step 2: Create a pivot table, as shown below

Step 3: Convert the pivot table to a formula

At this point the pivot table has become a formula. We can first observe the formula for the monthly sales of furniture,

=CUBEVALUE("PBID",$E7,F$5)

We then replace the contents of the referenced cell to get the following formula

=CUBEVALUE("PBID",CUBEMEMBER("PBID","[产品].[类别].&[家具]"),CUBEMEMBER("PBID","[Measures].[AC SALES]"))

This formula is how we want to get the target data in CUBE.

The CUBEVALUE formula of three parts:

which model? Answer: "PBID".
Under which dimensions? Answer: "[Dim Product].[Product Category].&[Technology]".
What is computational logic? Answer: "[Measures].[Model.Sales]".

With these parameters, the CubeValue function will start from the CUBE called "PBID", under the dimension of " [Product].[Category].&[Furniture] ", press " [Measures].[AC SALES] " logic to calculate the value and return it.

When you see this formula, everyone may be afraid. It is difficult to write such a long formula. In fact, in the process of writing the formula, there will be text prompts to assist you to complete the writing of the formula. Like the picture below.

At present, we already know two ways to get data through the model, one is the pivot table, and the other is the cubevalue function. By using these two methods flexibly, various custom reports can be obtained.