5 Replies Latest reply on Sep 22, 2017 6:18 AM by Jim Dehner

Calculate total in 2D table

Hi,

I have difficulties to calculate the total in a table when the data field is stretched to 2 dimensions. Please see example:

I have raw data below, each sale contains a few items with the sales figure as a percentage out of the dollar amount of each sale.

I have made a calculated field in tableau called ItemSales to provide the dollar amount for each item (Percentage * Sales), then I have this field in a 2d table with Country and Sector in each dimension.

What I trying to achieve is to turn this to a percentage figure against the total ItemSales listed in this table, and what displays in the table is dynamic and controlled by the Sale Number and Country checkbox filters.

However, I am struggling to calculate the total ItemSales in 2d, then can use it as a denominator to calculate the percentage.

To illustrate the problem, I currently have Table 1, and I want to turn it into Table 2. It is as simple as show value as  "% of Grand Total" in Excel!

I have attached the example tableau workbook (version 10.3). Thank you

• 1. Re: Calculate total in 2D table

hi

here is one way to do it

I start by calculating the fixed sales for each Sector and country group - the formula is an LOD fixed expression that creates a permutation of the dimensions that appear before the colon (:) and uses the formula after the colon to aggregate the data

in your case the formula is           { FIXED [Country],[Sector]:sum([Sales])}

next we use this to create a percentage -             sum([ItemSales])/sum([fixed extended sales])

the percentage and the fixed totals are applied at each row level - see below

Then using the Tableau Grand total function the table totals are determined - the calcualtion for percentage is applied to the column totals

Adding filters for sector and country provide cabability to limit the presentation per user selection while preserving the calculations

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 2. Re: Calculate total in 2D table

Hi Jim,

Thank you for the reply. Your solution provides a percentage of the sales item against each sale they belong to, however it does not provide the percentage of each category out of the total sales figure. To illustrate, I currently have Table 1, and I want to turn it into Table 2 in tableau.

Regards,

Sha

• 3. Re: Calculate total in 2D table

see attached

just added a table calc as shown

Jim

• 4. Re: Calculate total in 2D table

Thank you Jim It does turn the numbers into percentage, however the figures are not correct. I.e. for country CAN and Sector Materials, there is only one record in raw data with ItemSales \$20. The total sales on the table is the sum of the three sales: \$50+\$100+\$75=\$225, so the percentage should be \$20/\$225=8.89% instead of your result 16.67%.

Also I'd like to correct the percentage table (table 2) in my illustration earlier, sorry I didn't noticed it included the All line and made everything halved. The correct figures should be like below:

• 5. Re: Calculate total in 2D table

These are the numbers in the model -

I have done what I can for you

Good luck with the solution you seek

Jim