9 Replies Latest reply on Mar 2, 2016 6:17 AM by Manjiri Virkar

    Calculating Grand Total while using Parameter

    Manjiri Virkar

      Hi,

       

      I am using parameters to display data and not filters. When I use the inbuilt functionality of calculating “Grand Total” it does not give me the correct value when I change the radius parameter (Select Radius) in Sheet 2.

       

      Can you please help me out with this?

       

      version: Tableau 9.0

       

      Regards,

      Manjiri

        • 1. Re: Calculating Grand Total while using Parameter
          Manjiri Virkar

          Joe Oppelt could you please help me out? It is the same work sheet with radius parameter, but this time, I need to calculate grand total with parameter as viewing criteria.

          • 2. Re: Calculating Grand Total while using Parameter
            Joe Oppelt

            Do you want the grand totals to reflect only those rows that are displayed?  You are getting 5480 instead of 2360 for Capacity, for example, because Tableau still sees the other rows (Austin, Bay area, etc.) and is adding it all up.

             

            That's because a table calc is being used as a filter.  When a table calc filters a sheet, it doesn't remove rows from the table like a quick filter does.  It just hides rows.  So the "Grand Total" function from ANALYSIS just adds it all up.

             

            And when Grand Total tries to total up a column that is a calc field, it tries to do the calc on the total numbers.  I have no idea what Tableau is going to do with that [Distance From Origin] calc when it tries to apply it to the Grand Total.

             

            So what you want to do create your own grand total calcs.  I created Calculation2 to do the grand total of distances, but only for those rows that are displayed on the sheet (as determined by the value of [Filter Distance].)  I threw it in the Measure Values shelf in Sheet2 just so you can see what number it found.

             

            You'll want to do the same for the other totals.  Do a WINDOW_SUM of only those rows that have a [Filter Distance] of 1.  And then display only those values on your dashboard as a separate sheet.

             

            For now, I have attached a sample of what I am describing.  If this will do what you need, I can describe the details of what I did.  Take a look at the dashboard and let me know if that's what you need.  I did two of the totals on there.

            • 3. Re: Calculating Grand Total while using Parameter
              Manjiri Virkar

              Hi Joe,

               

              Sheet 4 is exactly what I need. Thank you very much for the solution

               

              However, when I try to incorporate those changes and with a Table(down) calculation, I get the total of all the rows and not the filtered rows using parameter selection. I am unable to share that file due to confidential data. I have attached the screen shots.

               

              Can you please tell me where I maybe going wrong?

               

              Thanks and Regards,

              Manjiri

              • 4. Re: Calculating Grand Total while using Parameter
                Joe Oppelt

                Right.  That's part of the detail I need to discuss.  Now that I know this is what you need, I can go into all that.

                 

                First of all, I initially added these extra calcs to the Measure Values list in Sheet 2, just to prove out that I was doing them right.


                To make Sheet 4, I duplicated Sheet2, and then dragged all the individual measure names from "Measure Names" to the Data Shelf so that they're still on the sheet for all the Grand Total calcs to work.  So you'll see in Sheet 4 that there are only two calcs currently in "Measure Values", but Vacancy and Capacity, etc., are up in the Data shelf.

                 

                When you right click on Calculation2 in the Measure Values shelf and do EDIT TABLE CALC, you'll see that the Compute-using is set for Table(Down) for Calculation2.  Above that is a pull-down box.  When you have a table calc that is built on another table calc, you need to make sure that all the "Compute Using" settings are set for ALL the underlying calcs as well.  Tableau gives you this pull-down to let you set all the table calcs that are involved in this final number.  In this case, [Distance from Origin] is also involved, and it needs to be set the way it is set in Sheet2 to get the proper computation.  On Sheet2 it's an advanced setting addressing all the dimensions, and restarting NONE.  Set it that way on Sheet4 as well.  So what you will end up with is [Distance From Origin] with and advanced setting, and to sum up all those individual Distance totals, you add Table(down).


                And you'll notice that in Calculation2 I don't just do WINDOW_SUM on the [Distance] calc.  I only include those rows that have [Filter Distance] = 1.

                 

                After that I did one more step.  When I put Calculation2 on Sheet2, I get a value for each displayed row.  When I move all the row-level table calcs to the Data shelf in Sheet4, I end up with multiple copies of the Grand Total calcs.  But we want to see only one value, not x-many.  In sheet4 I am using a filter called [just get one index].  I created a calc called [Index].  It's a useful technique to help you see what Tableau is doing with the display of table calcs.  Play with it some time in your application.  It's a table calc as well, and it lets you see how Tableau is "walking" through the table.  You can mess with the "Compute Using" settings to see how the index numbers change, for example.  What I'm doing in [just get one index] is telling Tableau only to display the first index that gets displayed on the sheet.  In the Filter shelf, edit that table calc.  You'll see that THREE table calcs get set up here.  And one of them is the [Distance from Origin] calc, and once again, it is set with the Advanced settings.

                 

                Play with this and let me know if you can make it work in your own application.

                • 5. Re: Calculating Grand Total while using Parameter
                  Manjiri Virkar

                  Hi Joe,

                   

                  Thank you for the explanation. It is indeed well explained!

                  I am getting the right numbers now. I did not do the Advanced Calculations on Distance from Origin. But, I am unable to display a single row. I dragged the "Just get one index" calculated field in the filters shelf and applied the same edit calculations as Calculation 2. But it still shows multiple rows. Where would I be going wrong?

                   

                  Regards,

                  Manjiri

                  • 6. Re: Calculating Grand Total while using Parameter
                    Joe Oppelt

                    I would need to see the workbook to figure out what is missing or what can get rid of the other rows.  It works in the example, and that's the best I can do at this point.


                    BUT!!!  I have attached a hack you can do instead.  Look at Dashboard 4 in the attached.  I took the filter off sheet4 so that all 3 rows show up, but on the dashboard I have hidden all but the first row.  I created a text box and floated it over the extra rows.  I formatted the text so that the lettering is white.  (You can type anything you want in there.  When I use a text box to hide stuff, I add text to explain what it is there for, so that I can figure out what I did if I ever have to go back there a year later and do something in that dashboard.)  So the text is white so the user doesn't see it.  And I formatted the text box to be white as well, so the underlying stuff doesn't show through.

                     

                    It's a hack, but it gets the job done.

                    • 7. Re: Calculating Grand Total while using Parameter
                      Manjiri Virkar

                      Hi Joe,

                       

                      I tried the same change in the attached worksheet and even in this one it does not show up. Can you figure out what I am missing?

                       

                      Sorry for the trouble!

                       

                      The text box idea is great! But still I want to know where I am going wrong...

                       

                      Thanks and Regards,

                      Manjiri

                      • 8. Re: Calculating Grand Total while using Parameter
                        Joe Oppelt

                        Aha!

                         

                        Simple.  Right click on the filter.  ([Just get one...]).  Click on FILTER at the top of the list.  That edits the filter.  Currently it gets values from zero through 1.  you just want to get 1 through 1. 

                        • 9. Re: Calculating Grand Total while using Parameter
                          Manjiri Virkar

                          ohh, now i get it!

                           

                          Thank you very much Joe for your patience and solution!