Calculating the average using two columns in a drilldown table

This Klip shows you how to calculate the average using two columns in a drilldown table. For more information about using drilldown, see Using drill down in a Table component.

This Klip calculates the cost of sales by Country, Province, and Region.

This Klip uses the LOOKUP function and also uses the REF button to reference a formula in another column.

To build this Klip, you will:

  • Build a Table component
  • Create a LOOKUP statement
  • Configure a Table for drilldown

This Klip uses the following data source:

Step 1: Building columns in the Table component

Goal: Use the LOOKUP function to sum sales for Country, Province, and Region.

  1. Select Build a New Klip.
  2. Select the Table component.
  3. Select Use an existing data source from the library and select your data source.
  4. From the component tree, select Klip, and type a Klip Title.

Step 2: Enabling drill down for the Table component

Goal: To apply grouping to Country, Province, and Region and allow aggregation of other columns.

Before enabling drilldown ensure that the format for Country, Province, and Region columns is set to Text to allow grouping of these columns. All other columns must be formatted as Number and Decimal Places set to (3).

  1. Select the Table component.
  2. Select the Drilldown tab.
  3. Select Enable Drilldown and complete the following tasks.

Field

Required information

 1 For Group by select Country.
 1 For Configure other columns at this level select SUM for Country Average, Spend and Sales. Select hide for Province, Region, Province Average and Region Average.
 1 Select the Add Drill Level button.
 2 For Group by select Province.
 2 For Configure other columns at this level select SUM for Province Average, Spend and Sales. Select hide for Region, Country Average and Region Average.
 2 Select the Add Drill Level button.
 3 For Group by select Region.
 3 For Configure other columns at this level select SUM for Region Average, Spend and Sales. Select hide for Country Average and Province Average.
  Save the Klip.