Reference Table data in a formula

Your prices, rates, rosters and planning assumptions probably live in a Table, because that is where they are easiest to keep up to date. Cube formulas can read those values directly, so you don't 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. When several rows should come together into one number, such as every salary in a department, use TABLE_SUM, TABLE_AVG, TABLE_COUNT, TABLE_MAX, or TABLE_MIN. They work like SUMIFS, AVERAGEIFS and COUNTIFS.

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.
• Rosters and registers. Add up salary cost or count headcount by department from an employee roster, or total depreciation from an asset register.

These have something in common. Each one is detail 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. 

  TABLE_COUNT

   is the exception: it counts matching rows, so any column works.

• For Cube to fill in a value automatically, a match column needs to be set to Dropdown: Dimension and linked to a dimension. For any other kind of column you type the value into the formula yourself.

• For 

  TABLE_LOOKUP

  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…)

All six Table functions are written the same way. Only the function name changes.

Pick the function by what you want back:

Note: there are no angle brackets around Table functions. They are written the same way as IF() and ROUND(). In the formula editor, Insert Functions lists them under Tables and inserts the full template for you.

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 Table function comes back to it.

The first kind lines your Table up against your model. The second kind fixes a column to one value. With TABLE_LOOKUP, between them they need to account for everything that tells one row in your Table apart from another. With the other Table functions, they decide which rows get added up, averaged or counted.

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.

For TABLE_LOOKUP, 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 matches and those figures don't get a value. See Some figures are 0 or empty when you expected a value.

If several rows should match, because you want them added together, you don't need more match columns. Use TABLE_SUM instead.

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.

• Dimensions the Table has no column for don't affect the match. If your Table has no Entity column, every entity reads the same row, so the result shows up under every entity. That is expected.
• 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.

Note: naming a scenario also means Cube stops matching on scenario, which can change how many rows match. A TABLE_LOOKUP that worked before you added the scenario may stop returning values 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 every Table function at the lowest level of detail first, then adds the results up the hierarchy. Table functions always use 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.

It also means a formula that returns only a rate, such as TABLE_LOOKUP("Department Bonus Breakdown", "Bonus %", "Department") on its own, adds the rates together at totals. Multiply the rate by an amount, such as salaries, so the totals mean something. The same goes for TABLE_AVG, TABLE_MAX and TABLE_MIN: they're calculated for each figure, then summed at totals.

Examples

One column is enough

A Table called Department Bonus Breakdown holds one row per department:

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 bonus expense.

Two columns are needed

A Table called Product Breakdown holds a price for each product in each market:

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 those figures would not get a price. 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"))

Adding up rows with TABLE_SUM

A Table called Employee Roster holds one row per employee, with a Department column, an Entity column and an Annual Salary column. Many employees share a department, so a lookup would find several rows. To get monthly salary cost for each department and entity, add them up:

TABLE_SUM("Employee Roster", "Annual Salary", "Department", "Entity") / 12

To count heads instead, on a Headcount account:

TABLE_COUNT("Employee Roster", "Employee ID", "Department", "Entity")

To add up only part of a Table, fix a column to one value. On a Depreciation account, using an asset register:

TABLE_SUM("Asset Register", "Monthly Depreciation", "Department", ("Asset Class", "Hardware"))

A Table function inside a bigger formula

A Table function only affects its own part of a formula. In "Bookings" + TABLE_LOOKUP("Product Breakdown", "Price", "Product", "Market"), every figure still gets its Bookings. Where the lookup finds no row, that part counts as 0 and the formula returns Bookings alone.

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.

Some figures are 0 or empty when you expected a value

With TABLE_LOOKUP, this usually means more than one row matched those figures. A lookup can only return one value, so a figure that matches several rows doesn't get one. Figures that match exactly one row are not affected. Once recalculation finishes, you'll see a warning saying how many figures matched more than one row.

To fix it, either add match columns until every figure points at exactly one row (go back to How to tell if you have enough match columns), or, if those rows should be added together, switch to TABLE_SUM.

Some figures are empty and the rest look right

When nothing in the Table matches, the result depends on the function. 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 Market dimension includes Market - Other and the Table has no Market - Other rows, those figures come back empty from a lookup. 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.
• Changing the Table updates the formula. When someone edits a row, the formulas that read it recalculate.
• 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.

Related articles

• Formulas overview
• Common formulas and supported operations
• Use the formula editor
• Guide to formulas rollup logic
• Troubleshooting Formula Errors
• Build and use a table
• When should I use a dimension rollup, tag, or formula?