5 Replies Latest reply on Oct 12, 2012 7:10 AM by Jonathan Drummey

    Percentage Column

      Hi,

       

      I have the data from an Essbase Cube organized like this:

       


      JanFebMar
      Net Sales


      Manufacturing Costs


      Gross Profit


      ...


       

      What I need to achieve is to add a column after each month, showing the percentage of each P&L line in regards to the first line (Net Sales):

      Jan

      %
      Net Sales100
      Manufacturing Costs2020%
      Gross Profit8080%
      ...

       

      Any idea on how to achieve this? I am using Tableau only for a day now, and the documentation is not very helping on this topic.

       

      Thanks & BR

      Philipp

        • 1. Re: Percentage Column
          Jonathan Drummey

          Hi Philipp,

           

          There are a variety of ways to achieve the desired result in Tableau, however I'm not clear on how your data is organized. For example, is each month truly a different column or is it date a single dimension with different values? Could you post a packaged workbook (.twbx) with some sample data? If you're unable to do that, a screenshot at least of your Tableau workspace showing the Dimensions and Measures window would be helpful.

           

          Jonathan

          • 2. Re: Percentage Column

            Hi Jonathan,

             

            I can't give you the workbook since we are working with real data. But I attached a screenshot, hopefully catching everything important.

             

            BR

             

            PhilippTableau_Screenshot.JPG

            • 3. Re: Percentage Column
              Jonathan Drummey

              Hi,

               

              I don't believe the view you want is possible with the data in it's current form, which is why you would be having a harder time finding a solution in the documentation.

               

              When you are using Kennzahlnamen/Measure Names and Kennzahlwerte/Measure Values to build a text table, Tableau is creating a convenience "dimension" of the set of Measures that are on the Measure Values shelf. What you're looking to do is to take those measure values, perform a per measure calculation on them, and create a second set of measures that would be displayed alongside the first set as a second column within each month. The problem is, while it's possible to use the value of Net Sales in a computation (since it's the first row), Tableau does not give us the ability to treat Measure Names/Measure Values as a dimension/value within a calculated field nor does it let us have a second instance of Measure Names/Values in the view, which would be necessary to show the new set of measures.

               

              Now, you could set up one worksheet that returns the values and the second that returns the % diff from Net Sales, and combine the two on a dashboard, however, you'd have to do that N times for the N months that you want to display.

               

              The general solution to this kind of problem is to reshape your data. I'm not familiar with Essbase, so I'm not sure how you'd set this up, here's the general process: For this kind of view, Tableau works better with "tall" data (lots of rows, few measures) rather than "wide" data (lots of columns), so I'd recommend reshaping the data. In other words, instead of having separate columns for 010 NET SALES, 020 GROSS PROFIT, there's a column for "Measure Type" that has values of "010 NET SALES", etc. and then a column called "Value" that holds the actual value. Once the data is reshaped, your desired view could be created in 11 mouse clicks:

               

              1. Drag the Date on Columns

              2. Drag "Measure Type" onto Rows,

              3. Drag "Value" onto the view, the default aggregation is SUM()

              4. Right-click on the "Value" green pill and choose Quick Table Calculation->Percent Difference

              5. Right-click again on the "Value" green pill and choose Compute Using->Measure Type

              6. Right-click once more on the "Value green pill and choose Compute Using->Relative To->First

              7. Drag "Value" from the Measures window once more onto the table area to finish creating your text table. By default, Tableau creates a Measure Names/Values table, with Measure Names on the Rows Shelf.

              8. Drag the Measure Names blue pill from the Rows Shelf onto the Columns Shelf to have your desired view.

               

              Jonathan

              • 4. Re: Percentage Column

                Hi Jonathan,

                 

                thank you for your detailed reply. Unfortunately, Essbase is a multidimensional database and Tableau only uses the dimensions it finds in the cube. It automatically uses the account dimension as value dimension, hence the dimension / value setup is given. Or is it possible to create such a normalized table in Tableau and then reference to the this table for visualization?

                 

                BR

                Philipp

                • 5. Re: Percentage Column
                  Jonathan Drummey

                  With relational sources, it's possible to convert Measures to Dimensions and vice versa. It seems like there might be something possible with Essbase as well, see this Tableau documentation: http://onlinehelp.tableausoftware.com/current/pro/online/en-us/help.htm. My thinking is that you might set up the datasource where the accounts are treated as a dimension and proceed from there using the method I outlined earlier. I have no idea whether this will actually work, though. One thought here is that you could end up with two datasources, one to build this specific view and then another datasource that works better for other reporting.

                   

                  In Tableau, you can build a view, then manually export data to Access or Excel, then re-import it as a new datasource for further use. There's also a white paper that someone published several months ago about automating this process using Tableau Server, I don't have a link handy. Those are the only ways I can think of.

                   

                  Jonathan