4 Replies Latest reply on Oct 14, 2018 5:10 PM by Jonathan Brough

    Table Calculation in Tableau

    Kelvin Nguyen

      Hello guys,

       

      Hope you guys can help me out here.

       

      I'm designing something that has a lot of comparison from this year to last year.

       

      For example, let's look at the following.

       

       

      I would like my text table to have (ideally) the first column as 2018 data, 2nd column as YoY% and (ideally) a third column that is "versus Budget" (I didn't have the budget data in this sample workbook but I guess if I figure out how to have the 2nd column, it's gonna be pretty easy to have the 3rd column).

       

      My second issue is that my data is storing everything as rows. You can see a snapshot below:

       

       

      Is that a good way to store this type of data? Or do you think the following is better (I'm really considering manipulate my data to something like this)?

       

       

       

      The difference is that I store account types (Sales, Cost, Service Cost, Service Income) as columns.

       

      I've attached my workbook in this post. Hope you guys can shed some light.

       

      Thanks guys.

       

      Kelvin

        • 1. Re: Table Calculation in Tableau
          Jonathan Brough

          Hi Kelvin,

          Shape of the data looks ideal to me.

          You could create the following calculations and plot those using measure names and measure values independently.

          Calc 1: Actual 2018 (or current year)

          Calc 2: Actual 2017 (or last year)

          Calc 3: YoY absolute

          Calc 4: YoY %

          Calc 5: Budget 2018 (or current year)

          Calc 6: Budget v. Actual 2018

          Thanks,

          Jonathan

          • 2. Re: Table Calculation in Tableau
            Kelvin Nguyen

            Hey Jonathan,

             

            Thanks for the input.

             

            I've tried your method and I've got exactly what it's meant to be, as the below:

             

            However, what if I want this table to have an extra row for Sales GP% (which is basically [Sales] - [Cost of Sales])?

            For Sales GP%, I will have Actual 2018 value, Actual 2017 value and also, difference versus last year. Is this even possible?

             

            And my second question is, hopefully you can help me out here, if I store data as below, does it help to improve performance and ease of calculations?

             

             

            Again, I've attached my workbook here for your reference.

             

            Thanks Jonathan!

             

            Kelvin

            • 3. Re: Table Calculation in Tableau
              Kelvin Nguyen

              sorry Jonathan Brough would you mind taking a look? Have you ever done something similar? Please let me know. Thanks Jon!

              • 4. Re: Table Calculation in Tableau
                Jonathan Brough

                Hi Kelvin,

                For the GP field, you could create a new Amount field that flips the sign from normal (positive) to negative for expenses. You could then sum up this new 'sign-adjusted' Amount field across all the accounts to derive your GP amount.

                If you added an account type of Revenue, Expenses etc, you could also add relevant subtotals.

                I do not know how you could add the GP% within the same view, but you could perhaps set it up on another sheet and then incorporate alongside the absolute figures within a dashboard.

                If the data is wide (your last screenshot) you can create calculated fields for GP and GP%. You may find it harder to change the structure later though.

                 

                Finally, here are some resources that may be of interest:

                 

                A reference sheet from Tableau structured around a relevant training course

                Tableau Reference Sheet: Accounting and Finance

                 

                A useful explanation of drill down in Tableau

                Balanced Sheet/ P&L - YouTube

                 

                Andy Kriebel's beautiful way to visualise some P&L elements on a dashboard

                Visualizing a Profit & Loss Statement

                 

                Thanks,

                Jonathan