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.
- Select Build a New Klip and add a Table component.
- Select your data source.
- 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 drill down 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).
- Select the Table component.
- Select the Drilldown tab.
- Select Enable Drilldown and complete the following tasks.
|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.|