5 Replies Latest reply on Dec 23, 2013 8:03 AM by Elizabeth Se

# PVMC Analysis

(ZN(TOTAL(SUM([Shipped Qty]))) - LOOKUP(ZN(TOTAL(SUM([Shipped Qty]))), -1))*(LOOKUP(ZN([Margin \$]), -1))*(LOOKUP(ZN([Prod Mix %]), -1))

Here is my current formula for volume, but the last part for product mix is throwing off the calculation.  Any ideas?

• ###### 1. Re: PVMC Analysis

Error message or incorrect results?

• ###### 2. Re: PVMC Analysis

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 <

• ###### 3. Re: PVMC Analysis

Hi Elizabeth,

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.

Thanks,

murali.govindu@gmail.com

• ###### 4. Re: PVMC Analysis

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.

• ###### 5. Re: PVMC Analysis

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

Excel?

On Mon, Dec 23, 2013 at 4:53 AM, Andrew Ball <