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:
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.
|Now that each row has the monthly total, Compute the % of Total for each month as shown below.
Jan%Total = @Jan@/@JanTotal@
|Hide the month total columns.|
In the end, your data will look like this: