5 Replies Latest reply on Apr 18, 2018 3:54 PM by Gabriel Z.

# Calculating YTD Price Realization - Year to Date Price Calculations

To all fellow Tableau problem solvers,

I am relying on this great community to find the answer to a burning Tableau problem.

I need to calculate price realization for a variety of product models in a Tableau table. For this, I must calculate YTD (Year To Date) sales and YTD units for each month this year and last year.

The YTD price realization calculation is (March example): MarYTD 2018 units * (MarYTD 2018 price - MarYTD 2017 price)

I need the monthly price realization which is: Mar YTD price realization - Feb YTD price realization

If the YTD number of units is zero either this year or last year, no price realization is calculated.

While this is a relatively simple Excel calculation, how do I solve this in Tableau? As my database grows, I believe Tableau can do this a lot more efficiently.

Attached the Tableau package as well as the Excel source file, with the calculation explained in detail. Note: the data is fictive.

Very much appreciate all the solutions provided. The best would be if you could attach the solution in Tableau format, since I am really new to Tableau and might not be able to translate text into Tableau actions very well at this stage.

• ###### 1. Re: Calculating YTD Price Realization - Year to Date Price Calculations

Hi

see the attached

these are the formulas the table calculations are all calculated like this

the price realization is calculated like this

an it returns this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 2. Re: Calculating YTD Price Realization - Year to Date Price Calculations

Hi Jim,

Thank you for the fast and elaborate reply, I really appreciate it as it is a great starting point for my analysis.

The YTD host units match the results in the Excel file. Why are the YTD sales and also price realization figures different from the Excel calculation? I'm trying to deconstruct, but have not found the answer yet.

Thank you,

Gabriel

• ###### 3. Re: Calculating YTD Price Realization - Year to Date Price Calculations

See the attached

I have been able to reproduce all the values at the product model level

The issue was a just plain error on my part in one of the formulas

AND caulation order on all of the table calcualtions

I dropped this in as a check and then kept it

then did this for the Price realization

then YTD calcs are set like this

the avg price is set like this at the highest level - the 2 lower levels are set like above (it is a nested table calc)

at the highest level the price realization is set like this

now all this is great at the product model level -

but Tableau Table calculations are difficult to get to sum down - they wan to continue to use the formula based on the summed variables rather than the sum of the individual calculations

the usual approach is to do the summation on a separate sheet and bring them together on a dashboard

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 4. Re: Calculating YTD Price Realization - Year to Date Price Calculations

Hi Jim,

This is outstanding! First I did not think this could be replicated in Tableau. Then I thought it would take me months and you got this far so rapidly. I am impressed.

If you could still share your knowledge please on the following, that would help me a lot:

a) The file now calculates YTD price realization (columns AI-AK in the Excel file). Do you have a solution for calculating monthly price realization (columns AM-AO in the Excel file)?

b) I need to display the Grand Total for YTD price realization as the sum of what was previously calculated at the model level. I should get -44K in Jan, 9.3K in Feb and 1.36M in Mar for the YTD price realization calculation to match the Excel file. However, the formula is applied on overall product (Aerotech), which introduces a product model mix which I would like to exclude. How do I replicate this total from Excel to Tableau? Attached 2 pictures (Tableau vs Excel)

Thank you so much for your kind help! I'm very excited about the Tableau path I have just started.

Best regards,

Gabriel

• ###### 5. Re: Calculating YTD Price Realization - Year to Date Price Calculations

Hi Jim, Hi All,

I have been able to calculate monthly price realization. Jim, your solutions helped me a lot to get this far so fast. It's my first week of Tableau and I feel like I am making progress.

Now, I need to show the total price realization by summing the results I have calculated, but without applying the same calculation (which Tableau seems to do by default). How do I do this?

This is what I mean:

I would like to replicate the result from Excel:

I have attached both the Excel source file as well as the updated Tableau packaged file.

I would greatly appreciate replies to be through the Tableau packaged file since I am so new and this helps me learn fast.

Thank you,

Gabriel