5 Replies Latest reply on Sep 9, 2016 11:26 AM by Shinichiro Murakami

# Create Total as a Row

Hello All

I am trying to see whether we can add a row called Total which can be an aggregate of all Rows.

For ex: In the Superstore Sample there are 3 entries in the Category Column : (1) Furniture (2) Office Supplies (3) Technology

Now in the same Category Column  I need another entry called "Total" which is Sum of all 3 elements (Furniture;Office Supplies;Technology )

I have tried Sets etc but its not working.

Also I need to see if this a % Calculation then SUM rollup wont work. hence need Aggregate etc .. Any ideas ?

• ###### 1. Re: Create Total as a Row

This is what the problem is. For ex: I am using the Profit Ratio calculation. I am able to put a row for Total using this technique. But when I make a chart of it then Total inst visible !

(1) able to get have Grand Total

(2) not able to see Grand Total on Charts

• ###### 2. Re: Create Total as a Row

The easiest way to get Total on top of each category is that

using 2nd axis to show Total.

At first you will see 100 % overlaid picture, but you can remove color from 2nd axis.

Sync Axis.

Put measure name pill to color shelf and you can choose color

and you can also change the name in the legend.

This is sum of all profit / sum of all sales across all category with below formula.

[Profit Ratio]

sum([Profit])/sum([Sales])

You can see that is aggregated value in the shelf with "AGG(**)".

Thanks,

Shin

1 of 1 people found this helpful
• ###### 3. Re: Create Total as a Row

The previous person was on the right track but a simpler version is to just do a dual axis. So for your example, you currently have 1 sum(sales) pill in the rows shelf. also, you have a category pill in the colors mark.....this is good for the first part.

From there, duplicate sum(Sales) so you have 2 pills on the row shelf. then, make it go to a dual axis. note: sometimes Tableau will change the chart type to a bar or bubble once you create a dual axis. you can manually force them back to a line chart.

Then, in the marks shelf...got to the 2nd sum of sales and make sure you do NOT have the category dimension on the color shelf, so it's only 1 color.

1 of 1 people found this helpful
• ###### 4. Re: Create Total as a Row

Thanks Jason & SHINICHIRO. This did help but one small question. I do have a prompt which shall also show the Total.

There is the ALL in the prompt but if I click that I only want the Total Line to be visible.

Once again, thanks for your kind help .. !!

• ###### 5. Re: Create Total as a Row

That requires complicated settings and I don't recommend it.

Instead, you can click color legend and highlight Total only.

Shin