Dimensions, variables and formulas

Breakdown a variable by dimensions

Hover over a variable and click on the Dimensions button to add a dimension breakdown to a variable.

If the variable already has a dimension breakdown, it will be coming from its connected data item/s (if the dimension exists in the data), or from its formula (if the formula refers to a variable that has the dimension).

You can also create new variables when viewing a dimension in the bottom-panel of the spreadsheet (once you open the dimension from the left-hand sidebar). Click on the + in the far-right column, and create a new dimension variable by choosing its format from the drop-down (e.g. Currency, %).

Dimension breakdowns & variable formulas

When a variable is broken down by a dimension, and you set a formula at the top (aggregate) row, it will be automatically applied in the same way across all dimension items. This is powerful as it means you only have to set the formula once (less duplication, less errors!).

If however, you wish to set a different formula/value for certain dimension items, simply edit the formula for that particular row/cell. These edits will show up as an Override, and you can easily reset the override (back to the default formula), by hitting "Reset override ↩️" or "Reset row ↩️".

If you do not have a top (aggregate) row formula/value, then you have no "default", so different formulas/values for dimension item rows/cells are not considered Overrides.

Re-ordering dimensions on a variable

You can easily drag to reorder the dimension breakdown on a variable. For example, if you have a variable broken down by Department and then Region, you can swap the order around so it is Region and then Department instead. This will not change any calculations, it will just change the dimension ordering that you see when you expand the variable.

Variable modifiers: Dimensions

Dimension modifiers allow you to slice & dice variables within formulas, using the related Dimensions and Dimension items. You can:

• Group by linked dimensions
• Filter by dimension items
• Aggregate away dimensions

For a deep-dive, see: Dimension modifiers.

Referencing dimension items within formulas

You can use your model's dimensions and dimension items in formulas.

• You can access the number of dimension items by clicking on the three dots and selecting Size:

• You can also use dimensions in if-statements (use this to dynamically define a variable's dimensional values without requiring a formula for each dimension item)

Sorting and Filtering Dimensions in the bottom panel

You can also dynamically filter and sort dimensions in the bottom panel (after opening the dimension from the left-hand panel). You can sort either by clicking Sort/Filter, or from the column header directly. If you sort the dimension items, you have the option to save the order, in which case it is applied to any variables in the models that have that dimension applied.

Sorting by dimension item value on a variable

You can also sort by dimension item value on a variable in the spreadsheet. For instance, if you have Spend broken down by Vendor, you can quickly sort the Spend variable so the breakdown is from biggest to smallest vendor.

Choose to sort based off the values for the Entire Model Period, or just the Last Actual Date, and choose Descending/Ascending order (Descending is the default - biggest at the top!).

Find this setting in the "More Options" menu on a variable, or ⌘/ctrl + 4. To remove the sort just hit the 'x' next to Entire Model Period / Last Actual Date.