2 Replies Latest reply on Jun 12, 2013 7:26 AM by Shawn Wallwork

    Calculation Trouble

    Benjamin Maulding

      Hey everyone, I am new to Tableau. I have been trying to figure out how to write an equation. I cant figure out how to do the valuation equation in Tableau due to aggregate and no-aggregate fields.  This is a dummy excel sheet which has been simplified to display the problem.

        • 1. Re: Calculation Trouble
          Shawn Wallwork

          Hey guys, I'm working this one. Bit of education to do so it'll take a minute.

           

          --Shawn

           

          PS: Benjamine, in the meantime read this KB article: Preparing Excel Files for Analysis | Tableau Software

          1 of 1 people found this helpful
          • 2. Re: Calculation Trouble
            Shawn Wallwork

            Benjamin, first a couple of housekeeping things, please

            • Post a packaged workbook with your dummy data already set up. You're more likely to get a response if you don't make us do all the grunt work for you. Thx.
            • Also if you are going to post an Excel file, make sure it's already 'prepared for Tableau', again to make our job easier. (See first tab of attached.) Collectively we've got quite a bit of knowledge around here, but we're pretty lazy folks (well at least I am), so please do most of our work for us. Thx.

             

            The valuation formulas you included were very helpful, so it was good that you gave us those, otherwise I couldn't have answered this question.

             

            OK, in Tableau the SUM() aggregate doesn't always mean SUM like you think of it in Excel. Depending on how your table is laid out the aggregates SUM() AVG() MIN() MAX() can all return the same value because there is only one value in the cell, so there's nothing to aggregate. This is one way to get around the aggregate/non-aggregate mismatch.

             

            In the attached file I included this table calculation:

             

            (SUM([Stores Base])/SUM([Stores Total]))/
            (TOTAL(SUM([Stores Base]))/TOTAL(SUM([Stores Total])))
            
            
            


            SUM([Store Base]) = C2 (in your Excel file)

            SUM([Store Total]) = F2

            TOTAL(SUM([Store Base]) = C15 (This is the same as summing a column to get the grand total)

            TOTAL(SUM([Store Total]) = F15 (Same as above)

             

            We don't need the * 100, because when you set the number format to percent Tableau automatically does the * 100 for you.

             

            Lastly, since Joe Mako and Jonathan Drummey are both always preaching to avoid using relative addressing (Table Down), I went ahead and set the addressing to advanced with the two fields you need. I'll let them jump in and explain the whys. (They'll also jump in if I got any of this wrong. )

             

            Hope this helps,

             

            --Shawn

            1 of 1 people found this helpful