
1. Re: Trouble with Subtotals of a calculation with percentages
Łukasz Majewski Apr 6, 2016 11:46 AM (in response to Cristina Palo)Try: SUM([SAF %]*[Total Ship $])
Cheers,
Łukasz

2. Re: Trouble with Subtotals of a calculation with percentages
Cristina Palo Apr 6, 2016 1:03 PM (in response to Łukasz Majewski)Still not working

3. Re: Trouble with Subtotals of a calculation with percentages
Cristina Palo Apr 8, 2016 10:44 AM (in response to Cristina Palo)Can someone help please?

4. Re: Trouble with Subtotals of a calculation with percentages
Łukasz Majewski Apr 8, 2016 11:32 AM (in response to Cristina Palo)Can you attach your workbook / sample (twbx)?

5. Re: Trouble with Subtotals of a calculation with percentages
Simon Runc Apr 8, 2016 11:46 AM (in response to Cristina Palo)hi Palo,
Now this was driving my crazy!! as I couldn't get to your exact number, but could in Excel...after a coffee I realized that (an I did the same!) when I copied the data you posted (via Excel on the way through) it rounded the 10.5% to 11%
I could see it was close, but couldn't see why it wasn't exactly correct!
So Łukasz is correct, in his answer.
On the 5.1% in the total. If you look at the attached you'll see a field called [Saf % (with Total)] with the following formula
IF MAX([Vendor #])= MIN([Vendor #]) THEN MIN([Saf %]) ELSE
SUM([SAF $])/SUM([Total Ship $]) END
The IF MAX..=MIN is a way to identify rows from totals and so handle them differently. If you want more info on how/why it works I suggest Jonathan Drummey's 3 part post on controlling grand totals

6. Re: Trouble with Subtotals of a calculation with percentages
Cristina Palo Apr 8, 2016 1:49 PM (in response to Simon Runc)OMG for a second I thought I had it!! Wow you are good...awesome solution to differentiate the total with MIN and MAX...however (obviously you would not know that because I did not post a package before ) the fields I am working with are from different data sources and not only that they are already aggregated... Can this get any more complicated?
Simon Runc/ @lukasz majewski  attached is a package tableau  hopefully there is still a way around to achieve what I need for this report. Below is expect output
Vendor # Total Ship $ SAF $ Saf % 14931 $2,231,011 $44,620 2.00% 15176 $0 $0 2.00% 16565 $535,890 $53,589 10.00% 16715 $350,523 $21,031 6.00% 19430 $714,303 $75,002 10.50% 30043 $249,039 $24,904 10.00% 31031 $63,267 $1,265 2.00% Grand Total $4,144,033 $220,412 5.32% 
7. Re: Trouble with Subtotals of a calculation with percentages
Łukasz Majewski Apr 8, 2016 2:25 PM (in response to Cristina Palo)Data blending complicates things. Are the two data sources excel files or completely different rdbms? If you could replace the 2 extracts with 1 joined dataset it would be a simple SUM()

8. Re: Trouble with Subtotals of a calculation with percentages
Łukasz Majewski Apr 8, 2016 2:37 PM (in response to Łukasz Majewski)Due to blending on [Vendor #] we loose that dimension when that total is calculated. Therefore the correct value can be calculated but not in the Grand Total Row...
Here it is Calculation 2 or 3:
It is as if that dimension ceased to exist and the blending cannot be made on that level so overall sum from the secondary source is taken  I think  which is quite unfortunate...
Avoid data blending if you only can

9. Re: Trouble with Subtotals of a calculation with percentages
Łukasz Majewski Apr 8, 2016 3:34 PM (in response to Łukasz Majewski)1 of 1 people found this helpful 
10. Re: Trouble with Subtotals of a calculation with percentages
Cristina Palo Apr 11, 2016 8:02 AM (in response to Łukasz Majewski)Thank you so much...you are right this is going to be a complicated solution! It works for now though...I am going to try to simplify the data sources so others can easily replicate this workbook!
Really appreciate all the help!
Cristina