10 Replies Latest reply on Apr 11, 2016 8:02 AM by Cristina Palo

# Trouble with Subtotals of a calculation with percentages

Hit a wall on this one...

SAF \$ = Total Ship \$ * SAF% which varies by Vendor. Can't seem to get an accurate Grand Total.

When I use SUM([SAF %])*SUM([Total Ship \$])  the total is much bigger than expected as it's summing up the % and then multiplying by the Total Ship \$

How can I get the following to return \$225,944 instead of \$1.8M?

Help please! Thanks!

Cristina

Desired output

 VENDOR # Total Ship \$ SAF % SAF \$ 14931 \$2,355,994 2.0% \$47,120 15176 \$151,639 2.0% \$3,033 16565 \$535,890 10.0% \$53,589 16715 \$350,523 6.0% \$21,031 19430 \$714,303 10.5% \$75,002 30043 \$249,039 10.0% \$24,904 31031 \$63,267 2.0% \$1,265 Grand Total \$4,420,655 \$1,878,778 \$225,944 5.1%
• ###### 1. Re: Trouble with Subtotals of a calculation with percentages

Try: SUM([SAF %]*[Total Ship \$])

Cheers,

Łukasz

• ###### 2. Re: Trouble with Subtotals of a calculation with percentages

Still not working

• ###### 3. Re: Trouble with Subtotals of a calculation with percentages

Can someone help please?

• ###### 4. Re: Trouble with Subtotals of a calculation with percentages

Can you attach your workbook / sample (twbx)?

• ###### 5. Re: Trouble with Subtotals of a calculation with percentages

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

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

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

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

OK, can be done ... but it was very tricky:

1 of 1 people found this helpful
• ###### 10. Re: Trouble with Subtotals of a calculation with percentages

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