SAS Visual Analytics Report Example: Percent of Total – For All, For Rows, and For Columns
Calculating the Percent of Total for your data is a common report requirement. SAS Visual Analytics offers a Percent of Total for all out of the box and I wanted to use this article to show you how you can also create custom measures to calculate a Percent of Total at both the row and column level when using a crosstab object.
The following examples walk through using a crosstab object with one category data item assigned to the row role and one category data item assigned to the column role. Let’s take a look at what I mean when I talk about the Percent of Total for all, for rows, and for columns. When talking about the percent of total, it all depends on the total amount you want to compare the pieces.
Descriptions for Percent of Total Types
Percent of Total – For All
Percent of Total - For All, which is the out of the box derived calculation available in SAS Visual Analytics, means that for every visible cell, take the percent of total compared to the grand total (sum) of the rows and columns. Here is a visual to help illustrate this concept: each individual cell (blue) divided by the grand total (red), then multiplied by 100.
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
Let’s look at an individual example in the below screenshot. The numerator is highlighted in blue, the denominator is the grand total highlighted in red and the result is highlighted in purple. (Yes folks, that’s no mistake, red and blue make purple.)
Percent of Total – For Rows
Percent of Total - For Rows, means that for each cell, take the percent of total based on the row’s total sum. Here is a visual to help illustrate this concept: each individual cell (blue) divided by the row’s total (red), then multiplied by 100.
And here is an individual example. Notice that I did rename the percent of total metric to be representative of the row’s data item assignment.
Percent of Total – For Columns
Percent of Total - For Columns, I’m sure you can spot the trend, means that for each cell, take the percent of total based on the column’s total sum. Here is a visual to help illustrate this concept: each individual cell (blue) divided by the column’s total (red), then multiplied by 100.
Here is a look at an individual example. Again, notice that I named the percent of total metric to be representative of the column’s data item assignment.
Steps to Configure Percent of Total Types
Percent of Total – For All
Like I mentioned earlier, this calculation is available out of the box with SAS Visual Analytics. Let’s check out how to generate it.
From the Data pane, select the measure you want the percent of total for. Then right-click on the measure and select New calculation…. Then in the Create Calculation window, use the drop-down menu and select Percent of total – Sum. You can keep or change the auto-generated name.
The new measure will appear under the Aggregated Measure grouping in the Data pane. Use the chevron to expand the properties and you can change the format precision or change the data item name.
Now we can add the Order Total (% of Total) to the crosstab object. Yep - it's that easy.
Percent of Total – For Rows
The easiest way to be sure we have the correct expression for the percent of total for the rows is to break down the numerator and denominator expressions. This way we can, according to what I learned in math years ago, “show our work”, and verify our final expression is correct.
As a reminder, we need each cell divided by the row’s total sum.
Let’s first define the numerator expression, as shown in the blue highlight above, where it must return each cell value. From the Data pane, click + New data item and select Calculated item.
Then build this expression. Since we are interested in the measure Order Total that is what we will Sum _ByGroup_. Notice that this operator is grouped under the Aggregated (simple) operators.
Now to show our work, let’s add it to the crosstab object. Notice that the new data item, Product Line (% of Total) – Numerator, highlighted in blue, matches each cell. Success! We can now trust this expression to represent the numerator of our target percent of total for row expression.
Next, we need to build the denominator expression. This is where we will use a new aggregated operator, AggregateTable, introduced in the SAS Visual Analytics 8.3 release. This aggregated operator allows us to build, if you will, a subquery of the data available in the current visual, select the type of aggregation and which by groups to include, exclude, or fix. See the SAS documentation for additional details about flexibility of the AggregateTable operator.
Like with the numerator, let’s build out a new measure so that we can show our work. From the Data pane, click + New data item and select Calculated item.
Then build this expression. This expression will sum the visible data for Order Total for the row while removing the category, or by group if you want to think of it that way, Vendor Loyalty Program which gives us the row total for each Product Line.
If I add this new data item to the crosstab object to show our work, we can see that for each row’s total, we get the correct value.
This means we can now trust both our numerator and denominator expressions to give us the intended values and we can put them together for our final Percent of Total – Row expression. Like in the previous steps, build a new data item by using the Data pane, click + New data item and select Calculated item.
Then build the expression that combines both the numerator and denominator using the x / y operator under the Numeric (simple) grouping. The percent format handles multiplying the result by 100 for us.
And voila! We have our row’s percent of total expression.
Percent of Total – For Columns
The expressions used to build the aggregated measures for the percent of total for columns are very similar to the percent of totals for rows in the previous example.
Recall from the earlier screenshot, the denominator will now be the total sum for the column.
The numerator expression, as shown in the blue highlight above, must return each cell value. Create a new data item by using the Data pane, click + New data item and select Calculated item.
Build this expression using the Sum operator under the Aggregated (simple) grouping. I’m still using the data item Order Total as the measure.
Add the new data item to the crosstab to show our work and so that we can verify the expression is returning the expected values.
Now we need to build the denominator expression. We will use a similar expression to the percent of total for row denominator and use the AggregateTable operator. However, before we wanted to remove the column by group to sum the row values. This time, for the percent of total for columns, we will remove the row by group data item, Product Line so that we can return the sum for the column.
Like before, let’s build a new measure so that we can show our work. From the Data pane, click + New data item and select Calculated item. Then build this expression.
This expression will sum the visible data for Order Total for the column while removing the category, or by group, Product Line which gives us the column total for each Vendor Loyalty Program.
When I add this new data item to the crosstab object to show our work, we can see that each row has the correct column total.
This means we can now build our Percent of Total – Column expression. Like in the previous steps, build a new data item by using the Data pane, click + New data item and select Calculated item.
Then build the expression that combines both the numerator and denominator using the x / y operator under the Numeric (simple) grouping.
Success! We have our column’s percent of total expression.
Conclusion
SAS Visual Analytics provides many out of the box expressions including the Percent of Total. This out of the box expression uses all the data in the crosstab object and assigns the grand total as the denominator. In this article, I have outlined how you can use one of the advanced aggregated operators, AggregateTable to generate either the row or column total so that you can better control which percent of total you would like to include in your report objects. All of the operators under the Aggregated (tabular) grouping are powerful tools to help build precise expressions for your reports. For an example of the AggregateCells operator see this article: VA Report Example: Moving 30 Day Rolling Sum.
Hi John Frytz,
There is a way you can have percentages on the columns and actuals in the totals – the only drawback is that we cannot currently dynamically format the number. So the best solution will be to have no format and to use the Comma format. The feature you will need to create is a Scoped Aggregated Measure.
The first thing to do is determine the Intersections to define the scoped aggregations for. The easiest way to do this is by defining Display Rules for the object you are wanting to use as the visual.
Then, for your new Calculated Item, you can define the necessary scopes. Just use the plus button in the upper right corner every time you need to add a scope to the calculation. If you do not define a scope for an Intersection you have in your object, the base expression will be used.
Hope this helps!
Thank you,
Teri
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
