Try: SUM([SAF %]*[Total Ship $])
Still not working
Can someone help please?
Can you attach your workbook / sample (twbx)?
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
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%
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()
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
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!