BookmarkSubscribeRSS Feed

SAS Visual Analytics Report Example: Percent of Total – For All, For Rows, and For Columns

Started ‎03-30-2020 by
Modified ‎01-26-2022 by
Views 16,209

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.  

 

01_PercentOfTotal_Total.png

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.)  

 

02_Total_BrokenDown.png

 

  

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.  

 

03_PercentOfTotal_Row.png

 

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.  

 

04_Row_BrokenDown.png

 

    

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.  

 

05_PercentOfTotal_Column.png

 

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.  

 

06_Column_BrokenDown.png

   


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.  

 

07_NewCalculation.png

 

 

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.

 

08_PercentOfTotal_Sum.png

 

Now we can add the Order Total (% of Total) to the crosstab object. Yep - it's that easy.  

 

09_PercentOfTotal_Total.png

 

   

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.

 

10_Row_Reminder.png 

 

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.  

 

11_NewCalculatedItem.png

 

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.  

 

12_Row_Numerator.png

 

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.  

 

13_Row_Num_Example.png

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.  

 

14_Row_Denominator.png

 

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.  

 

15_Row_Denom_Example.png

 

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.  

 

16_Row_Expression.png

 

 

And voila! We have our row’s percent of total expression.  

 

17_Row_Expression_Example.png

 

    

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.

 

18_Column_Reminder.png 

 

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.  

 

19_Column_Numerator.png

 

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.

  

20_Column_Num_Example.png

 

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.

 

21_Column_Denominator.png 

 

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.  

 

22_Column_Denom_Example.png

 

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.

 

23_Column_Expression.png  

Success! We have our column’s percent of total expression.  

 

24_Column_Expression_Example.png  

 

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.

         

Comments

thank you very much for posting this. It save me a lot of time.

Is there a way to retain percentages on the columns and actuals in the totals only?

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.

 

01_UseCommaFormat.png

 

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. 

 

02_UseDisplayRulesToFindIntersections.png

 

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. 

 

03_DefineScopedCalculations.png

 

Hope this helps!

Thank you,

Teri

 

 

@TeriPatsilaras  Thanks. I'll try it out

Version history
Last update:
‎01-26-2022 04:16 PM
Updated by:
Contributors

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Tags