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

    Calculating YTD Price Realization - Year to Date Price Calculations

    Gabriel Z.

      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
          Jim Dehner

          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
            Gabriel Z.

            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
              Jim Dehner

              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
                Gabriel Z.

                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)YTD Price Realization - Tableau Totals.pngYTD Price Realization - Excel Totals.png

                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
                  Gabriel Z.

                  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:

                   

                  Monthly Price Realization - Excel Totals.png

                  Instead, Tableau is showing this:

                   

                  Monthly Price Realization Tableau Totals.png

                  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