Concepts → Level Based Measure
About a Level Based Measure
A level based measure is a measure that performs a calculation at a predefined level of aggregation and can be evaluated based on one or more levels of aggregation (i.e. grouping dimension). On a Dashboard tab, a defined level based measure displays as a Column in a table insight.
A level based measure enables you to:
- Evaluate measures grouped by different levels of aggregation in the same tabular insight.
- Analyze the contribution percentage of one aggregation level with respect to another (e.g. a country’s contribution percentage to the regional revenue).
In the Formula Builder, when using a groupBy parameter for a level based measure, you must include all the leading grouping dimensions in the insight from the top down to the level based measure to be evaluated.
Example
Consider a scenario where you want to analyze the sales revenue by the following aggregation levels:
- Region
- Subregion
- Country
In this case, you must use a level based measure to calculate the following:
- Revenue based on the Region level
- A country’s contribution percentage with respect to the Regional revenue
- Revenue based on the Subregion level
- A country’s contribution percentage with respect to the Subregion revenue
Use the following steps for detailed instructions on how to use a Level Based Measure:
In the Cluster Management Console (CMC), you can create a tenant that includes Sample Data. The Sample Data includes the SALES schema.
In the Navigation bar, select the Content tab, and then select + New → Add Dashboard.
In the Add Dashboard dialog, for Name, enter Revenue Dashboard, and then select Add.
In the Action bar, select + (add icon), or select + Add Insight.
In the Insight panel, select Listing Table or V.
In Tables, select Aggregated Table.
In the Data panel, select Manage Dataset (+).
In the Manage Data Sets panel, in Tables, select SALES.
From the Data panel, drag and drop the following fields from the COUNTRIES table to the Grouping Dimension tray:
- Region
- Subregion
- Country
Drag and drop the Revenue column from the SALES table to the Measure tray.
- If the Properties panel is not already open, select > to the right of the Revenue pill to open the panel.
- In Format, select Dollar Rounded.
From the Data panel, drag and drop Add Formula to the Measure tray.
The Formula Builder automatically opens:
In Search Functions, select the down arrow, and then select Aggregation Functions.
Double-click the first sum function,
sum(double[] exp, groupBy)
, to add the formula to the editor.In the Formula Editor, replace double[] exp with Revenue and dimension, ... with Region from the Data panel.
sum(SALES.SALES.AMOUNT_SOLD,groupBy(SALES.COUNTRIES.COUNTRY_REGION))Select Validate & Save.
In the Measure tray, double-click the New Formula pill and rename it to Regional Revenue.
- Select > to the right of the Regional Revenue pill to open the Properties panel.
- In Format, select Dollar Rounded.
From the Data panel, drag and drop Add Formula to the Measure tray.
In Search Functions, select the down arrow, and then select Aggregation Functions.
Double-click the second sum function,
sum(double[] exp)
, to add the formula to the editor, and then divide it by the first sum function,sum(double[] exp, groupBy)
.Replace both occurrences of double[] exp with Revenue and dimension, ... with Region from the Data panel.
sum(SALES.SALES.AMOUNT_SOLD)/sum(SALES.SALES.AMOUNT_SOLD,groupBy(SALES.COUNTRIES.COUNTRY_REGION))Select Validate & Save.
In the Measure tray, double-click the New Formula pill and rename it to Contribution % to Regional Revenue.
- Select > to the right of the Contribution % to Regional Revenue pill.
- In Format, select Percent.
Redo the same analysis for Subregion Revenue; repeat the last 4 steps above and replace Regional Revenue with Subregion Revenue.
- Subregion Revenuesum(SALES.SALES.AMOUNT_SOLD,groupBy(SALES.COUNTRIES.COUNTRY_REGION,SALES.COUNTRIES.COUNTRY_SUBREGION))
- Contribution % to Subregion Revenuesum(SALES.SALES.AMOUNT_SOLD)/sum(SALES.SALES.AMOUNT_SOLD,groupBy(SALES.COUNTRIES.COUNTRY_REGION,SALES.COUNTRIES.COUNTRY_SUBREGION))
- Subregion Revenue
The subregion is of lower hierarchical level than the region. When using a groupBy parameter, you must include all levels of the higher hierarchy leading to the subregion level (i.e. region in this case).
- Name the insight Sales Revenue Analysis.
- In the Action bar, select Save.