Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- SAS Communities Library
- /
- SAS Visual Analytics Report Example: Percent of Total – For All, For R...

Options

- RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content

- Article History
- RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content

Views
16,033

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

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

**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**, 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**, 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.

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.

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.

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.

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

04-02-2020
03:22 PM

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content

04-02-2020
03:22 PM

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

09-30-2021
07:30 PM

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content

09-30-2021
07:30 PM

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

10-05-2021
07:14 PM

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content

10-05-2021
07:14 PM

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

10-06-2021
03:14 PM

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content

10-06-2021
03:14 PM

@TeriPatsilaras Thanks. I'll try it out

Are you ready for the spotlight? We're accepting content ideas for **SAS Innovate 2025** to be held May 6-9 in Orlando, FL. The call is **open **until September 25. Read more here about **why** you should contribute and **what is in it** for you!

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

Article Labels

Article Tags

- Find more articles tagged with:
- GEL