Grid Page: Create Custom Calculations Based on Data from Multiple Models

On a grid page, you can create formulas that include values from multiple models.

Prerequisites

Your story should have two or more models (data sources).

Context

Grid pages are useful for when you want to create your own custom formulas based on data from different models. There is no need to link the models together: just pick an empty cell and create your formula.

When you swap the axes of a table or drill down, the formula automatically updates with the new cell reference.

Note

If you change the model after you create the formula, you will see REF_ERR instead of the formula. Select the cell containing the formula to see which referenced cells are no longer valid.

Changes to the model include the following actions:
  • The referenced cell is part of a hierarchy; collapsing the hierarchy hides the cell.
  • Add more dimensions or measures to the table.

When you change the table back to its original layout, the formula will show the correct result.

Example

You are discussing purchases with your colleagues. Someone wonders if there is a way to show the combined sales per month for dissimilar products.

You create a grid page in your story and add two models to it: drinks and athletic gear. You limit both models to the same time period (first half of 2015, all months) and to one specific part of the country.

Procedure

  1. Add a grid page.
  2. For each model in your story, add a table to the grid page.
  3. Expand your tables so that you can see the cells that you want to use for the formula.
  4. Select an empty cell in the grid.
  5. Type in your formula.

    Add together the total sales for both products for each month: =C4+C15

    Copy the formula to the remaining monthly cells.

  6. (Optional) Calculate the average sales per month: =sum(E15:E20)/6

Results

When you select one of the calculation cells, the value for the formula is displayed.