Computing % of Total

June 28, 2017

Computing % of Total

Running totals and windowed aggregates are best handled in a Business Intelligence (BI) tool which allows for sophisticated aggregation capabilities.  However, there are times when you need an aggregate of a column at the data prep layer because other formulas rely on this aggregate value or the data isn’t going to be used in a BI tool. This tip introduces you to the concept of a “vertical” computation, using a % of Total computation as the example.  The approach is very simple. Even if you are not interested in % of Total, per se, this approach can be useful in countless ways.

 

Assume your dataset looks like this:

 

4 column by 3 row simple table

 

Your goal is to compute each fruit’s contribution as a percent of total.

 

Starting with a project pointing to the base dataset above, follow these steps:

  Compute a column named “ID”, where every row has a value of “1” – this is going to be our left “key” in the lookup
  Click the Attach > Lookup Tool & choose the same dataset as the base dataset in your project
  Click the “expand lookup steps” in the Lookup step
  Make the “Import step” active by clicking on it.
Click the Compute Tool and compute a new column called ID2 with a value of “1” – this will be our right “key” in the lookup.
  Click the Shape Tool.
 Select Group By at the top of the Shape editor. Group the data by ID2 and SUM each of the columns (Jan, Feb, Mar sum to Jan Total, Feb Total, Mar Total respectively in our example). Notice, there is only 1 row of data as a result of the Group By operation.
Click the Lookup steps and direct the connection to be ID = ID2

As a result of this self lookup, your aggregate values will appear on each row, allowing the value to be referenced in a formula.

Click Save
Now that each row has the monthly total, Compute the % of Total for each month as shown below.

Jan%Total = @Jan@/@JanTotal@

calculating % of total

  Hide the month total columns.

In the end, your data will look like this:

Show Buttons
Hide Buttons