Your prices, rates and planning assumptions probably live in a Table, because that is where they are easiest to keep up to date. Cube formulas can now read those values directly, so you no longer have to copy them into the cube or retype them every time they change.
Use TABLE_LOOKUP when a formula needs to pull one number out of a Table. It works the way VLOOKUP does in a spreadsheet: point at a table, say how to find the right row, and get a value back.
What you can use it for
- Price lists and rate cards. Calculate revenue from a price list your team already maintains, instead of typing prices into formulas.
- Bonus and commission rates. Work out compensation expense from a rate table, with each department or role on its own rate.
- Exchange rates and conversions. Keep one rate table and let every entity read from it.
- Allocation percentages. Spread overhead and shared costs using percentages you maintain in a Table rather than in accounts.
These have something in common. Each one is a rate that changes on its own schedule, is often owned by someone outside finance, and would otherwise have to be kept in two places at once.
Before you start
- The Table needs to already exist, and you need access to it.
- The column you want a value from has to be a number column.
- For Cube to fill in a value automatically, that column needs to be set to Dropdown: Dimension and linked to a dimension. For any other kind of column you will type the value into the formula yourself.
- The columns you match on need to point to a single row. There is more on this in How Cube finds the right row.
How to write it
TABLE_LOOKUP("table name", "column to return", match columns…)| Part | Required | What it does |
|---|---|---|
| Table name | Yes | The Table you want to read from. You can add a scenario in square brackets to always read one version of it. See Working with scenarios. |
| Column to return | Yes | The column holding the number you want back. It has to be a number column. |
| Match columns | Yes | How Cube finds the right row. You need at least one, and usually more than one. |
Note: there are no angle brackets around TABLE_LOOKUP. It is written the same way as ABS(), ROUND() and CEIL().
How Cube finds the right row
You can write a match column in two ways, and they behave differently. This is the part worth spending a minute on, because almost every problem with a lookup comes back to it.
| How you write it | Example | What happens |
|---|---|---|
| Just the column name | "Department" |
Cube fills in the value for you. Each figure looks up the row for the department it belongs to, so Sales gets the Sales row and Marketing gets the Marketing row. This only works on columns linked to a dimension. |
| The column and a value | ("Market", "APAC") |
Cube uses the value you typed, every time. Every figure in the formula reads the APAC row, whichever market it actually belongs to. |
The first kind lines your Table up against your model. The second kind fixes a column to one value. Between them, they need to account for everything that tells one row in your Table apart from another.
How to tell if you have enough match columns
Write down what makes a row unique in your Table. In a price list that is often product and market together, because the same product appears once for each market. In a bonus rate table it may just be department.
Every one of those columns needs to appear in your formula, either as a column Cube fills in for you or as a value you type. If one is missing, more than one row will match and the formula returns 0.
A few details worth knowing
- Cube matches on the dimension a column is linked to, not on what the column is called. A column named "Cost Center" that is linked to Department will match on Department, so you can label columns however reads best.
- A column that is not linked to a dimension always needs a value from you. Leave it out and the formula will not save.
- You can also type a value for a column that is linked to a dimension. Doing that changes the behavior, so
"Market"and("Market", "APAC")are not the same thing even though they name the same column. - Renaming a Table or a column never breaks a formula that uses it.
Working with scenarios
A Table can hold a different version of itself for each scenario, so a formula needs to know which version to read.
| What you write | What it reads |
|---|---|
"Product Breakdown" |
Whichever scenario you are looking at. A figure in your forecast reads the forecast version of the Table. |
"Product Breakdown[Actuals]" |
Always the Actuals version, whatever scenario you are looking at. Useful when the value should be the same everywhere, as a bonus policy or an exchange rate often is. |
Note: naming a scenario also means Cube stops matching on scenario, which can change how many rows match. A formula that worked before you added the scenario may start returning 0 afterwards. And if the Table has no version saved in the scenario you named, the formula will not save.
How totals are calculated
Cube works out the lookup at the lowest level of detail first, then adds the results up the hierarchy. This uses the Calculate leaf values, then sum rollup setting.
In practice this means a total is the sum of the calculations underneath it, not a single lookup done at the total. If each department has its own bonus rate, total bonus expense is every department's bonus added together. The overall percentage that results will not appear anywhere in your Table, and that is expected.
Examples
One column is enough
A Table called Department Bonus Breakdown holds one row per department:
| Department | Bonus % |
|---|---|
| Sales | 30.00% |
| Marketing | 20.00% |
| Product Team | 20.00% |
| Customer Success | 15.00% |
| Professional Services | 18.00% |
| Executive | 35.00% |
Department on its own is enough to find a row, so you only need one match column. On a Bonus Expense account:
"6010 - Salaries & Wages" * TABLE_LOOKUP("Department Bonus Breakdown[Actuals]", "Bonus %", "Department")Each department's salaries are multiplied by its own bonus rate, and the results add up to total compensation.
Two columns are needed
A Table called Product Breakdown holds a price for each product in each market:
| Product | Market | Price |
|---|---|---|
| Enterprise | APAC | $100,000 |
| Enterprise | Central | $80,000 |
| Enterprise | East | $75,000 |
| Enterprise | West | $75,000 |
| Premium | APAC | $80,000 |
| … | ||
Product on its own is not enough here, because Enterprise appears four times, once per market. Matching on product alone would find four rows and return 0. Match on both:
"Units Sold" * TABLE_LOOKUP("Product Breakdown", "Price", "Product", "Market")Fixing a column to one value
To price everything at the APAC rate no matter which market it belongs to, type the value instead of letting Cube fill it in:
"Units Sold" * TABLE_LOOKUP("Product Breakdown", "Price", "Product", ("Market", "APAC"))If something is not right
The formula will not save
Cube checks these things when you save, so a formula with one of these problems never produces a figure at all. Any problems it finds are listed together.
| What happened | What to check |
|---|---|
Cube does not recognise the Table name, for example "Dept Allocation Breakdown" is not a valid table name
|
Check the spelling, and check you have access to the Table. |
| A column is not in that Table | This covers the column you asked for and any column you are matching on. Compare the spelling against the Table. |
| A column needs a value and does not have one | Columns that are not linked to a dimension need you to type the value, like this: ("Column", "Value"). |
| The Table has no version in the scenario you named | Either save a version of the Table in that scenario, or take the scenario out of the formula. |
Everything comes back as 0
This means more than one row matched somewhere. When that happens the whole formula returns 0, and you will see a warning once the recalculation finishes. Figures that did find a single row come back as 0 as well, so the whole column is affected rather than just the part that was ambiguous.
To fix it, add match columns until every figure points at exactly one row. Go back to How to tell if you have enough match columns and check that nothing is missing.
Some figures are 0 and the rest look right
When nothing in the Table matches, the lookup returns 0. There is no warning for this, because finding nothing can be a perfectly reasonable answer.
The usual reason is a dimension member with no row in the Table. If your Department dimension includes Engineering and the Table has no Engineering row, Engineering comes back as 0. Add the missing rows, or confirm that leaving them out is what you intended.
Other things to know
- You cannot delete a Table a formula is using. The same goes for individual columns. Cube tells you how many formulas are using it, so you can remove those first.
- Renaming is always fine. Renaming a Table or a column will never break a formula.
- Saving takes longer than it used to. When you edit a Table a formula reads from, Cube recalculates that formula too. The save runs in the background and shows up in your task list. On a large Table this is noticeably slower than before. It is working normally, so there is no need to save again.
- People can see the result without seeing the Table. Someone who cannot open your Table will still see the figure it produces in a report, but cannot click through to the Table behind it. They also cannot write their own formulas against a Table they do not have access to.