
1. Re: Calculate total in 2D table
Jim Dehner Sep 21, 2017 1:57 PM (in response to Sha Cheng)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.

Sample.twbx 35.3 KB


2. Re: Calculate total in 2D table
Sha Cheng Sep 22, 2017 1:27 AM (in response to Jim Dehner)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
Jim Dehner Sep 22, 2017 5:31 AM (in response to Sha Cheng)see attached
just added a table calc as shown
Jim

Sample.twbx 34.4 KB


4. Re: Calculate total in 2D table
Sha Cheng Sep 22, 2017 6:08 AM (in response to Jim Dehner)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:
