Error message or incorrect results?
Couple of issues. The theory of the formula is correct but right now it
returns $0 for volume, which is incorrect.
The data is organized by item, product group, and then product category.
The formula should be
(total volume for current year - total volume for the prior year) *
(margin $ for the item for the prior year* product mix % for the prior year
I can't get the formula to reference the grand totals for the first part of
the calculation or reference the item level data for margin$ or mix %.
On Fri, Dec 20, 2013 at 4:20 PM, Aaron Clancy <
I browse several times the forums to educate myself, and minimal times I will be able to answer but for sure will attempt to resolve. It would be nicer if you could attach a packaged workbook for everybody here (you may mock in case what you have is a sensitive data for sharing) so that you may get an opinion which could turn out to be an answer for you.
One of the problems you will be having is that the "LOOKUP" function is a table calculation, so needs to work on aggregated fields.
Both the [Margin $] and [Prod Mix %] fields need to be aggregated in some form in order for the calculated field to work. I think in your case, you can aggregate using MIN, MAX or ATTR. If all the records have the same value, these will leave the value unchanged, but allow the table calculation to work.
Thanks! The fields are already aggregated though and I need the actual
calculation to return instead of min or max. The mix % varies for each
product. How do I lock references in Tableau formulas like you can in
On Mon, Dec 23, 2013 at 4:53 AM, Andrew Ball <