10 Replies Latest reply on Jul 16, 2015 7:07 AM by Rody Zakovich

# How to add Column "% Total"

Hi everyone,

I have the Exel file as the Attachment. I have already created Column "Total" for Sale Category A,B and C. Now i want to have Column "% Total" beside the Column "Total" like the Exel file.

• ###### 1. Re: How to add Column "% Total"

Hello Phuviet,

An example workbook would be beneficial here.

Is A, B and C in one Column? Or are they 3 separate columns?

What version are you on? If 9.X, then we could use LoDs, if not we can use a combination of Window Calcs.

Regards,

Rody

• ###### 2. Re: How to add Column "% Total"

Hi Rody,

I want to create a WorkSheet whick Looks exactly the same as the Exel File.

• ###### 3. Re: How to add Column "% Total"

Hello Phuviet,

I understand, but how you accomplish this depends on the data structure, and which version you are on.

If A, B and C are different columns, than you can create a calc like

SUM(ZN(A) +ZN(B) + ZN(C) )

To get Row Totals.

Then, if you are on V9, you could create a calc like

SUM(ZN(A) +ZN(B) + ZN(C) ) / { SUM(ZN(A) +ZN(B) + ZN(C) )  }

To get the % of Totals, but if you are on V 8.3, you'll have to use a window calc.

If A, B and C are in the same column, then things change slightly.

Regards,

Rody

• ###### 4. Re: How to add Column "% Total"

Hi Rody,

A,B,C are in different Columns. I know how to calculate % Total. But I dont know how to place the Column "%Total" beside the Column "Total" in Tableau?

Its difficult to make it with Tableau

• ###### 5. Re: How to add Column "% Total"

Hello Phuviet,

I can get you to this

But I don't think this.

Is possible in Tableau using a single sheet. The only way we can add a "Row" into a crossbar using Tableau us using the Row and Column Totals in Analysis. But here, you are essentially trying add two Totals, which (To my knowledge) we cannot do.

Regards.

Rody

1 of 1 people found this helpful
• ###### 6. Re: How to add Column "% Total"

Phuviet,

Doing things in Tableau that look like excel defies the use of a BI tool like Tableau. However, if you want it to look like excel you need to do some calcs.

You can create a calc and name it Total: ZN(sum([A]))+ZN(sum([B]))+ZN(sum([C]))

Then create a % total calc and name it % Total : [Total]/total([Total])

Change the default properties of the % total calc to reflect 2 decimal percentage. If you double click the new calcs they are brought onto the view. That takes care of your first requirement to bring % total calc next to the total.

To bring the % total calc on the row, I am not aware of a single sheet method. You can however create a window calc in the new sheet and bring the resulting sheets on to a dashboard to disguise Tableau to look like your excel file.

You will need 3 window calcs for each of your measures A, B and C like window_sum(zn(sum([A])))/total([Total])

See attached version 8.3 WB!

1 of 1 people found this helpful
• ###### 7. Re: How to add Column "% Total"

Hi Pooja and Rody.

Thanks for Answears. i have thought to create a second Worksheet for Row % Total also. But the Problem is that. When we filter some Data, the first Worksheet  with less Data will be not near the Worksheet for Row % Total as you did. And The Dashboard Looks not very nice.

So i will have to think of another Solutions.

• ###### 8. Re: How to add Column "% Total"

Hello Phuviet,

Since you are using these sheets on a Dashboard, you could Apply the Filter to both Sheets, this will ensure both are filtered together.

To ensure the sheets are kept in line, Create a placeholder Text field to put into the Column shelf of the second sheet.

% Total =>

' % Totals '

Then you could place the two sheets within a Vertical Container.

Regards,

Rody

• ###### 9. Re: How to add Column "% Total"

Oh, Thanks Rody,

I have forgot to use Vertical Container. It works

• ###### 10. Re: How to add Column "% Total"

Adding the Column Placeholder will give both sheets the same relative Width. So you can ensure the % total sheet stays in line with your Columns from the Crossbar Sheet.

Using a Vertical Container will ensure the % Total Sheet moves up, as you filter Dates from the Crossbar sheet.

Ignore the numbers, I didn't want to fix the LoDs for filters, but I wanted to show you that it works.

Regards,

Rody

1 of 1 people found this helpful