10 Replies Latest reply on Mar 24, 2017 3:00 PM by Shinichiro Murakami

# Creating a Calculation Based on Two Dimension Choices

Hi All!

I'm hoping that someone will be able to help me solve a problem I'm having.   I'm trying to create a % change calculation based on two UPCs that I am picking for comparison.

The base measure is price and I'm trying to create a line chart comparing two picked UPCs and then show the percent difference in price change between the two over a week by week time period.  The key here is that the user will be able to pick two UPCs to compare at any time with the % change result.   I've tried different ways of doing this, but each time I am not getting the result I need.  Is there a way to create a universal calculation that will allow a user to always pick two UPCs and see the results?

Attached is an example workbook file (ver. 10.1) that I am working with.  I'm hoping that someone will be able to help me out on this.

Thank you in advance for your response.

• ###### 1. Re: Creating a Calculation Based on Two Dimension Choices

Hey Milton,

A Table Calculation should be all you need to do the trick.

See the attached workbook to see how I computed it, and let me know if this is what you were after? Or is it different?

This would not work if someone selects more then two, and can only show one line "Percent Difference" between the two.

1 of 1 people found this helpful
• ###### 2. Re: Creating a Calculation Based on Two Dimension Choices

Hi Milton

Assuming users always pick two of dimensions, the read line is the score you are seeking. [Delta btwn Brand]

(ZN(SUM([Group A Price])) - LOOKUP(ZN(SUM([Group A Price])), -1)) / ABS(LOOKUP(ZN(SUM([Group A Price])), -1))

[Delta From previous]

(ZN(([Delta btwn Brand])) - LOOKUP(ZN(([Delta btwn Brand])), -1)) / ABS(LOOKUP(ZN(([Delta btwn Brand])), -1))   Thanks,

Shin

1 of 1 people found this helpful
• ###### 3. Re: Creating a Calculation Based on Two Dimension Choices

Hi Milton

See the attached - it does what you are looking for - It is ethically challenged - I tried straight up week over week and also 3 week rolling average

This is just brute force

• created 2 parameters - one for UPC 1 and the other for UPC 2
• Created calculated fields for the price of each chosen upc and the difference
• Then a separate calculation for % difference
• Also stripped the date from the w/e dated field and converted to an actual date so the time series could be plotted
• The first 3 calculated fields are on one marks card the % difference is on a second - dual axis connects then Let me know it this helps

Jim

1 of 1 people found this helpful
• ###### 4. Re: Creating a Calculation Based on Two Dimension Choices

Hi Travis,

Thank you for getting back to me.  I'm not seeing an attached workbook, so wondering if it was attached.

You can also send to my e-mail - mforde@daymon.com

Thanks again!

-Milt

• ###### 5. Re: Creating a Calculation Based on Two Dimension Choices

All three replies has attachment.

Though you cannot see the attachment from inbox view.

Please go to original post and find attachments.

Thanks,

Shin

• ###### 6. Re: Creating a Calculation Based on Two Dimension Choices

sorry Milton

It's Jim I forgot to attach it

I will also send it to you email

Jim

• ###### 7. Re: Creating a Calculation Based on Two Dimension Choices

Thank you Jim!

I received many options and all seem to be in the direction, I'm looking for.  Will play around with my data, but thank you again for your response and help.

-Milt

• ###### 8. Re: Creating a Calculation Based on Two Dimension Choices

Thank you Shin!

Many options received and trying them all out.

Appreciate the help in trying to solve my issue.

-Milt

• ###### 9. Re: Creating a Calculation Based on Two Dimension Choices

Thanks Milton

Glad to help out

Jim

On Fri, Mar 24, 2017 at 4:33 PM, Milton Forde <tableaucommunity@tableau.com>

• ###### 10. Re: Creating a Calculation Based on Two Dimension Choices

You are welcome.

Enjoy Tableau!!

Shin