DATATABLE
Creates a small table with the values typed into the formula.
DATATABLE(<name>, <type>, [<name>, <type>] ..., { { <value>, ... }, ... })Returns: A table.
What it does
DATATABLE creates a table from values typed into the formula. It suits a short list of assumptions, scenario rates, or a mapping that changes rarely. Because the values live inside the formula, the table is static. A change means editing the formula, not the source data. A typical use is a list of scenarios (base, upside, downside) that a slicer uses for a what-if measure.
Syntax and arguments
- name and type pairs: each column has a name in quotes and a data type, such as
STRING,INTEGERorDOUBLE. - value rows: each row sits inside its own pair of braces, with values separated by commas and matched to the columns in order.
Example
A scenario table with a growth rate for each scenario, used in a sales forecast.
Scenario Rates =
DATATABLE (
"Scenario", STRING,
"Growth Rate", DOUBLE,
{
{ "Base", 0.05 },
{ "Upside", 0.10 },
{ "Downside", -0.02 }
}
)
The table has one row for each scenario. A slicer on the Scenario column then drives a what-if measure, such as the forecast sales times one plus the selected growth rate.
Example: a second use
A mapping of accounts to report lines, typed in so that anyone reviewing the model can see the assumption.
Account Map =
DATATABLE (
"Account", STRING,
"Report Line", STRING,
{
{ "Product sales", "Revenue" },
{ "Returns", "Revenue" },
{ "Cost of goods", "Cost of sales" }
}
)
Connect this map to other data with TREATAS or LOOKUPVALUE, using the account name as the key.
Things to watch
- The values are fixed in the formula. Keep data that changes often in the source system, not in DATATABLE.
- Each value must match the type of its column. A mismatch causes an error.
- Keep the list short. It is for assumptions and mappings, not for data.
- In a calculated table, the rows are stored when the model refreshes.
- Write negative numbers with a minus sign inside the braces.
In business terms
Scenario rates for a forecast, a list of approval thresholds, or a small mapping of accounts to report lines. Putting the assumption in the model where reviewers can see it is often better than hiding it in a spreadsheet.