9 Replies Latest reply on Jun 30, 2015 2:14 PM by Joe Oppelt

    How to insert a calculated row into a crosstab?

    Michael Titera

      Hi,

       

      Version: Tableau Desktop 8.3

      Data Source: Microsoft SQL Server 2012 Analysis Services (OLAP Cube)

       

      Using BusinessObjects Analysis, I am able to simply add calculated rows like the one below (see red arrow). For each measure (Direct, Assigned Out, and Indirect) it is summing up the Activities A41000, A42000, A43000, A44500, and A49550 (not ".Revenue"):

      2015-06-24_12-33-37.png


      How can I do this simple task in Tableau?:

      2015-06-24_11-56-47.png


      Thanks much,


      Michael

        • 1. Re: How to insert a calculated row into a crosstab?
          Michael Titera

          Any help here would be appreciated.

           

          Thanks much,

           

          Michael

          • 2. Re: How to insert a calculated row into a crosstab?
            Joe Oppelt

            Can you mock up a packaged workbook with data shaped like your actual data set?

            Your question has a whole lot of "it depends" to it.

             

            Working from a physical example will be a whole lot easier to understand your requirements.

            • 3. Re: How to insert a calculated row into a crosstab?
              Michael Titera

              Thanks for the response, Joel.

               

              Since the data is from an OLAP cube, I am not sure how to create a packaged workbook with the data. In the simplest form, the requirement is basically just how to create total lines in a crosstab. For example, how would I simply calculate and insert the total rows below (in red)?:

              2015-06-30_11-53-27.png

              Thanks,

               

              Michael

              • 4. Re: How to insert a calculated row into a crosstab?
                Joe Oppelt

                Tableau works on the data as it is read into the internal table.  And it works uniformly on all the rows, so the concept of inserting what looks to Tableau like random rows isn't going to fly.

                 

                Having a way in your table to specify what should be aggregated is the way you want to tell tableau to do what you want.

                 

                So in the attached example I created a simple excel spreadsheet that looks like this:

                 

                ActivitieslevelDirectIndirect
                revenue010000
                a1151
                a2151
                a3151
                a4215
                a5215

                 


                I created an additional dimension called level that lets me group [Activity] values.


                then in Sheet1 (attached) I put both LEVEL and ACTIVITIES on the sheet, and told the Analysis tool to add all subtotals, and it uniformly did a subtotal at each level.  (I told it to hide the levels column, so you don't see the individual level values, but you get a total at each level.)  The one drawback that differs from your expected output is that there is a subtotal for Activity="revenue" as well as for the various other clusters.  Tableau wants to do this uniformly.

                 

                In Sheet2 I played with a calc field that sums up the totals for a1+a2+a3.  But you're not going to insert that in a separate row.  You can't create rows that aren't already in your incoming data.  You can display it other ways, and you can use it in other calcs, and that might be of value to you elsewhere in your application.  I could have also done this calc using the [level] field, and that would actually have been less complicated, but I wanted to show you a way to do those totals manually if you can't (or don't want to) add a [level] column to your existing data.

                2 of 2 people found this helpful
                • 5. Re: How to insert a calculated row into a crosstab?
                  Michael Titera

                  Thank you very much, Joe!

                   

                  That's a bummer that Tableau is not capable of doing custom calculated rows like BusinessObjects Analysis can.

                   

                  I do like your workaround in Sheet 1, where you added a level dimension and then had Tableau perform subtotals on each level. However, is it possible to rename each Total line to a unique name? For example, the first Total line would be named "Total Revenue", the second Total line would be named "A1+A2+A3 Total Expenses", and the third Total line would be named "A4+A5 Total Expenses":

                  2015-06-30_13-14-40.png

                  Thanks,

                   

                  Michael

                  • 6. Re: How to insert a calculated row into a crosstab?
                    Joe Oppelt

                    If you change the labels, it works uniformly for all the total labels.

                     

                    See attached.  I changed the name to "  sub Total".

                     

                    I also edited "row banding" so that rather than each row alternating shading, now each level (and its total) alternates shading.  At least that makes it a little more obvious what each total means.

                    1 of 1 people found this helpful
                    • 7. Re: How to insert a calculated row into a crosstab?
                      Michael Titera

                      Thanks, Joe!

                       

                      So, you cannot have uniquely named Sub Total lines...bummer.   I'll have to see if these limitations are acceptable to my Business Partners...

                       

                      I do like the grouped row banding. Thanks!

                       

                      Cheers,

                       

                      Michael

                      • 8. Re: How to insert a calculated row into a crosstab?
                        Michael Titera

                        I am assuming the answer is no, but will ask anyways...

                         

                        If I wanted to insert a calculated row that would take the first Sub Total (1,000) and subtract the second Sub Total (15) and give me the Result (85), under the second Sub Total (see red arrow below), would that be possible?

                        2015-06-30_14-11-07.png

                        Thanks,

                         

                        Michael

                        • 9. Re: How to insert a calculated row into a crosstab?
                          Joe Oppelt

                          You can do the calculations, but inserting them in an excel-like manner is not how Tableau is designed.

                           

                          Usually when people want to formulate calculations like you asked about and display them below a crosstab, they create a second sheet, display the data with one sheet and the totals with the second on a dashboard.  But it looks like you want to have a series of data-followed-by-total chunks.  And even that could be possible except that it looks like your chunks of data are of varying sizes.

                           

                          Take a look at sheet 3 in the attached.  This is a simple visualization that lends itself to what you are looking to do.  Tableau excels in graphically displaying things, and you can put text and values in the viz.  Tableau does not excel at being Excel.

                           

                          Note also that when you scroll over a bar, you get a pop-up box of info.  It's called a tooltip.  You can do all sorts of magic in that box.  In the attached example I conditionally displayed a string depending on whether it is the first bar or any other.

                          2 of 2 people found this helpful