4 Replies Latest reply on Apr 12, 2012 11:40 AM by Jonathan Drummey

    Easy in Excel, impossible in Tableau? Calculation with absolute references

    Daniel Thull

      Hi,

      I have a problem with a calculation that's easy to do in Excel but not so straightforward in Tableau. At least not for me...

       

      I have a table with sales and profit by product that I would like to visualize instead of just using the existing Excel table I get from finance. In addition to sales and profit information by year, finance also provides an analysis of the drivers of the profit variance. This is really useful and I would love to visualize this!

       

      Attached please find an example Excel file. On the first tab you find the P&L table as I get it from finance. On the second tab I have reformatted this to be imported into Tableau.

      You see that the table contains a few formulas. For example: the profit variance driven by volume gets calculated as follows:

       

      Profit var. driven by volume = (Sales2010 - Sales 2009)*Total Profit 2009

       

      I would like to recreate these formulas within Tableau but I'm running into difficulties. For example, I cannot rely on table calculations as there are two different "scopes" within the same formula (sales difference between periods, profit across entire table). The attached packaged workbook contains my unsuccessful attempt.

       

      Does anyone have an idea how I could do this?

       

      Thanks,

      Daniel

        • 1. Re: Easy in Excel, impossible in Tableau? Calculation with absolute references
          Alex Kerin

          If the data set is exactly as shown, you could create calculated fields like  - SALES2010:

           

          if [period]=2010 then [sales] end

           

          and then with other calc fields do a (sum([Sales2010]) - sum([Sales2009]))*[TotalProfit2009]

          • 2. Re: Easy in Excel, impossible in Tableau? Calculation with absolute references
            Daniel Thull

            Alex, thanks for pointing me in the direction to use calculated fields to store aggregate values. I'm still struggling how I can combine the values of different years in one table though.

             

            In the attached workbook I followed your suggestions and created the fields for sales2009 and sales 2010. For the calculation I need to multiply their difference by the Total Profit value for 2009 (at company level) for each line. If I just create a TotalProfit2009 field by dividing profit by sales it will hold the 2009 profit margin for each product. I guess I could use a WINDOW_SUM but somehow I can't get it work (when I place [Period] on the Level of Detail shelf the other values disappear...).

             

            thanks,

            Daniel

            • 3. Re: Easy in Excel, impossible in Tableau? Calculation with absolute references
              Richard Leeke

              This is a classic example of an issue I go on about occasionally - which is that there is really a missing addressing function in table calculations. As well as FIRST(), LAST() and INDEX() you really need a THIS() to refer to the second context you talk about. This comes up a lot when you are trying to do correlation calculations.

               

              I know the Tableau folk considered having a THIS() function when they were first developing table calculations but opted not to because of adding complexity and the risk that it would make it too easy to generate really slow calculations (which seems a bit ironic seeing as the WINDOW_XXX() functions get really slow anyway for exactly the reason they were worried about - unless you add a lot of complexity to work around the issue).

               

              The only ways I have ever found of getting around the limitation are either hard-coding specific offsets in the way Alex is suggesting if the number of dimensions is manageable or by doing the correlation in SQL in the data source.

               

              This old thread of Alex's discusses the issue a bit more.

              • 4. Re: Easy in Excel, impossible in Tableau? Calculation with absolute references
                Jonathan Drummey

                Hi Daniel,

                 

                I don't feel totally clear on what you're doing, but it seems like you need to avoid using WINDOW_SUM() and use TOTAL() instead, and use different partitioning of the nested table calcs that make up the variance measures.

                 

                I created a TOTAL(SUM([Profit 2009)) measure called T Profit 2009. Then I used that and your existing Sales Variance measure that's [Sales 2009] to create the following "Profit variance due to volume (nested using total)" calc:

                 

                [Sales Variance]*[T Profit 2009]

                 

                Then after dragging that into the view I set the Compute Using for Sales Variance to be Period and for T Profit 2009 to be Table (Down). This measure has really big numbers (should that * really be a /?), so I set the number format to be Billions.

                 

                Setting the compute using for the T Profit 2009 measure to Period fails, I'm not sure why, I think it also has to do with why trying to calculate the profit using WINDOW_SUM() fails. There are two periods in the underlying data and the [Profit 2009] field is only returning rows for 1/2 of them, so when the Period is 2010 the total profit measures are returning either an empty value or 0 or Null (you can see this on the WS Profit 2009 along Table (Down) measure in the "3rd try - jtd" worksheet) and then the Profit variance is returning something like an empty value, Null, or 0 for the 2010 values of Period and then another value for 2009.

                 

                Does this help, or am I totally offbase here?

                 

                Jonathan