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).
Important

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:

Note

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 Revenue
      sum(SALES.SALES.AMOUNT_SOLD,groupBy(SALES.COUNTRIES.COUNTRY_REGION,SALES.COUNTRIES.COUNTRY_SUBREGION))
    • Contribution % to Subregion Revenue
      sum(SALES.SALES.AMOUNT_SOLD)/sum(SALES.SALES.AMOUNT_SOLD,groupBy(SALES.COUNTRIES.COUNTRY_REGION,SALES.COUNTRIES.COUNTRY_SUBREGION))
Note

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.