6 Replies Latest reply on Mar 22, 2012 11:32 PM by Kevin Lee

    Calculations to create fixed totals unaffected by filtering

    Kevin Lee

      Hi,

       

      I'm having some difficulty in creating fixed totals (using TOTAL()) that can is unaffected by any filters.

       

      For example, in my attached file, what I'm trying to create is a fixed "Grand Total" field, which is the sum of A Costs and B Costs, which are from two separate tables. A Costs can be filtered by "Yes?", which doesn't affect B Costs.

       

      The problem I have is that Grand Total changes if I use the "Yes" filter - as only the "Yes" A Costs are added to the Grand Total (and the "No" A Costs are filtered out). What I need is a Grand Total that remains the same regardless of how A Costs is filtered.

       

      I've tried using the TOTAL() calculation in "Grand Total" Measure (under "datasource2"). I've tried playing around with the different "Default Table Calculation" options, but wasn't able to get the result I needed.

       

      If someone could help me with this one that would be fantastic. Thanks in advance!

       

      Kevin

        • 1. Re: Calculations to create fixed totals unaffected by filtering
          Andy Cotgreave

          HI Kevin

          I'm not quite sure what you are after. If you look at the image below, what do you want to happen when I filter and just show the "Yes" values. Should all the values in the A+B column stay the same? Which of the Grand Totals on the bottom row should stay the same?

          Cheers

          Andy

          Sheet 3.png

          • 2. Re: Calculations to create fixed totals unaffected by filtering
            Kevin Lee

            Hi Andy,

             

            Thanks for your prompt response, and for presenting the data in a clearer way.

             

            What I'm looking to do is - create a calculated measure such that: eg. for Region B in Feb, A Costs +B Costs will always be 145 + 163 + 1981 = 2,295, regardless of whether the "Yes?" filter is set on "Yes", "No", or "All".

             

            Let me know if this makes any more sense - thanks again!

             

            Kevin

            • 3. Re: Calculations to create fixed totals unaffected by filtering
              Andy Cotgreave

              HI Kevin

              [I assume you got your arithmetic wrong - 145+163+1981 = 2289?]

               

              Check out the attached. There is a parameter which switches the Output sheet from this:

              No and Yes.png

               

              to this:

              Yes only.png

               

              How is this done? Well - it looks like you were on the right track with the TOTAL calcs. Some tips:

              1. Do as many calcs in the primary data source as you can - even if they are operating on the secondary data source

              2. I used a parameter as my filter - check out the [New yes or no] calc. That's an ATTR() on the Level of Detail. What does this mean? Well, I can add it and hide values rather than filter them ( move it to the rows, right-click and choose "Show hidden data" to see all the values). Hiding instead of filtering is the key when doing this kind of task.

              3. Partitioning is also important in this example!

              • 4. Re: Calculations to create fixed totals unaffected by filtering
                Kevin Lee

                Hi Andy,

                 

                Thanks alot. I'm amazed at how complex the solution is!

                 

                While I can't say that I can understand what exactly is going on, it seems to serve my purpose, so thanks alot! One thing I do need to know though is how the Yes? parameter works, and the "New Yes or No". The reason is that I'd like to be able to filter my data such that I only display "Yes and No" (in one column, not two separate ones), "Yes" and "No". Currently the solution only displays "Yes and No" in two separate columns, or only "Yes", but not only "No".

                 

                Thanks!

                 

                Kevin

                • 5. Re: Calculations to create fixed totals unaffected by filtering
                  Andy Cotgreave

                  Hi Kevin

                  Yes - this kind of thing can appear complex. It's partly because of the way Tableau uses filters. Once you untick something from a filter, the query Tableau sends to the underlying dataset explicitly excludes those records. As a result, when you filter out "No" records, they simply aren't in the result query, so Tableau cannot use them in totals.

                   

                  Using Parameters and hidden data is a useful technique to get around this. Yes, it's not straightforward, but a useful trick to have in your arsenal!

                   

                  How does it work?

                  Check out the help file on parameters for more info about how they work:

                  http://onlinehelp.tableausoftware.com/v7.0/pro/online/en-us/parameters.html

                   

                  The [New yes or no] calc looks at the parameter and either returns all Yes/No as "yes" or their actual values. Because I have HIDDEN the No results, the output table appears to be "filtering" the results as you change the parameter. In reality, the results are still there, but the hidden data is done as the last thing before drawing the table, giving you the result you want.

                   

                  Andy

                  1 of 1 people found this helpful
                  • 6. Re: Calculations to create fixed totals unaffected by filtering
                    Kevin Lee

                    Thanks Andy. I will do my homework and figure out how all this works!