5 Replies Latest reply on Mar 29, 2018 8:45 AM by Joe Oppelt

    Using Lookup and Grand Total in calculation formula

    Barry Sheridan

      Hi,

       

      I am new to using Tableau, and I am trying to make a calculation row below grand total.

       

      The row will take will take the count of null values of each month from the grand total.

       

      I presume the best way would be to use the lookup function(Contains "Null").  Or could I use an offset ?

       

      I would appreciate some help with the syntax of the calculation field.

       

        • 1. Re: Using Lookup and Grand Total in calculation formula
          Joe Oppelt

          There are a couple of "it-depend"s to answer your question.


          Can you upload a sample workbook?

          • 2. Re: Using Lookup and Grand Total in calculation formula
            Barry Sheridan

            Hi Joe.  Sorry, I wont be able to use the workbook above as it is sensitive data.  Would you able to tell me some things I must consider first ?

            Is it actually possible to use the grand total as I wish to ?

            • 3. Re: Using Lookup and Grand Total in calculation formula
              Joe Oppelt

              Too many "what-if"s.

               

              Consider anonymizing your workbook:

               

              Anonymize your Tableau Package Data for Sharing

               

              Or hack up a similar example using Superstore.

               

              The screen shot doesn't tell me anything about what fields are dimensions and which are measures, for example.  (I can deduce some, but not enough to answer anything.)

               

              I can tell you this:  You're going to end up making a second sheet with the totals, and positioning it on a dashboard so it is abutting the original data sheet.

              • 4. Re: Using Lookup and Grand Total in calculation formula
                Barry Sheridan

                You were right Joe, I have two separate sheets now one for the calculation and one for the original table.  So I was able to create the correct calculation field as seen in sheet 2 below.  My aim to create an additional calculation getting the second calculation represented as a percentage of the Grand Total.  I am presuming Tableau does not have the functionality to add the calculation in 2.1 to the table above.  See below:

                Kind regards,

                • 5. Re: Using Lookup and Grand Total in calculation formula
                  Joe Oppelt

                  I still can't know for sure without a workbook, but I am assuming [TimeToConnect] is a dimension. 

                   

                  Tableau is not excel.  It works on a dimensionally-oriented basis.  Therefore, what it does to one mark, it does to all marks.  The Grand Total line is a special feature that acts outside the dimensionally-oriented approach.  What you are looking to add to the sheet is one extra strip of cells that are outside the dimensional structure.  Excel will let you do that.  Tableau doesn't.  (Tableau has to be all things to all data sources, so it can't be excel-specific in its behavior.)

                   

                  So when we want to add in something that is outside the dimensional structure, we make a new sheet and abut it to the old one on a dashboard.


                  You can tack on a second row to the totals sheet by making a new calc, and then using the measureNames/MeasureValues technique for displaying a list of measures.  I'm assuming you made a calc to get that total value.  Make another one to get a percentage.