Values referenced by dashboard questions include not only the raw data but also calculated values from formulas and tags, which can lead to inadvertently duplicating or excluding data in your queries.
In addition, not all formulas created in Cube can be plotted as questions due to the roll-up logic applied. Consider this article's best practices when working with formulas and tags.
Avoid double counting values from formulas and tags
When visualizing with a dataset, including tags and formulas, set your filters to account for the correct value to avoid skewed numbers.
There are two different approaches to filtering these, described below, along with when to use each.
Filter calculated values
Dashboards recognize which values Cube generated on your behalf through the computation of a formula or tag. You can use the Calculated filter to include or exclude these values.
For example, to ensure your Dashboard only uses data directly loaded into Cube and excludes all formula or tag values, add the filter Calculated and set it to False.
Filter by tag
Use the filters to selectively include or exclude data associated with tags from a specific dimension using the additional dropdown in the filter selector.
For example, setting this additional filter option to is not and selecting particular department tag names is excellent when building a visualization involving formulas or tags from one dimension but not another. This ensures you still pull desired tag-based values from other dimensions (e.g., other departments or tags for Account) or formulas.
Learn more about creating and using tags.
Limitations of formula-based questions
Not all formulas created in Cube can be plotted as questions due to the roll-up logic applied.
Formulas that use the Calculate leaf values, then sum roll-up logic, can be used as a filter in a dashboard question, while formulas that use the Sum leaf values, then calculate logic, cannot. These formulas that use Sum leaf values, then calculate can be recreated as a custom expression to summarize by instead.
Learn more about Cube formulas.
For example, let's consider two formulas:
Gross Margin = Revenue - Cost of Goods Sold
This formula can be successfully plotted in a question simply by filtering to this dimension because Cube stores the result (Revenue - Cost of Goods Sold) in the leaf values.
The data warehouse powering visualizations only needs to sum up these child values for accurate results.
Gross Cash Burn Rate = Bank / Expense
This formula, however, cannot be directly visualized in a question.
Cube applies the Sum leaf values, then calculate roll-up logic, which means it only stores the pre-summed leaf values.
The final calculation of Bank divided by Expense occurs when data is fetched into your spreadsheet. Cube does not store the actual value you would see in the spreadsheet.
Therefore, when applying the filter to the dimension "Gross Cash Burn Rate," you would see the same values as in the Cube-enabled spreadsheet's drilldown, missing the final mathematical step.
Instead, you can filter to the dimensions "Bank" and "Expense" and perform the calculation in the summarize by section of the question as a custom expression.
In summary, the roll-up logic used in formulas determines whether they can be plotted as questions by filtering to the dimension and formulas with Sum leaf values, then calculate logic does not store the final computed values required for direct visualization.
Learn more about rollup logic used in formulas.
Ready to get started creating questions? See our How to build a standard FP&A dashboard for inspiration.