8 Replies Latest reply on Jan 9, 2019 12:05 PM by Joe Oppelt

    Show total when not filtered otherwise show individual values.

    Yul Beauchamps

      Hi folks,

       

      I just stated working with Tableau and I keep learning new things everyday but I could not find a solution to my current table.

       

      I have a simple line graph that shows number of records over a time period.  Then I added a district dimension to the color marks so I could see a seperate line per district.  However, I also wanted to see a line with the totals.  For that I created a duel axis which kind of gets me most of the way.

       

      What I really want is to be able to show a single line (total from all districts) when districts has no filter (set to all).  If any districts are selected then switch to individual lines per district without showing the total.

       

      Any ideas?

       

      Thanks,

        • 1. Re: Show total when not filtered otherwise show individual values.
          Ken Flerlage

          Any chance you could provide a sample workbook?

          • 2. Re: Show total when not filtered otherwise show individual values.
            Joe Oppelt

            Make two calcs:

             

            [Districts in data source] :

             

            {FIXED : COUNTD([District]) }

             

             

            [Districts on Sheet] :

             

            { EXCLUDE [dimension1](, [dimension2], ... [dimension-N]) : COUNTD([District]) }

             

            Then first one will tell you how many districts are in your data source, regardless of what filters you have on your sheet.

             

            The second one needs to have a list of all the dimensions on your sheet (including [District]!), and that will tell you how many districts were selected by the user.


            If they are equal, then the user selected ALL.  If not equal then the user has a partial list.

             

            Make two sheets.  One will have just the total line.  The other will have the separate lines by district.

             

            Then make a filter that compares the two calcs.  On one sheet, filter for the condition where the two are equal (the TOTAL line sheet), and for the other sheet filter for when they are unequal.  Use this to sheet swap.

             

            So I just suggested a ton of moving parts.  As a new user you might be overwhelmed with that.  So I suggest that you upload a sample workbook, and I'll help you with it.

            • 3. Re: Show total when not filtered otherwise show individual values.
              Yul Beauchamps

              I've actually played around with view switching on dashboards.  Was hoping this could be done on a single view.  I'd love to include a workbook but in this case the data is confidential.  Worse case I could create a new workbook with sample data instead...

              • 4. Re: Show total when not filtered otherwise show individual values.
                Joe Oppelt

                If you want to do this in one sheet, then you'll need to have a [District] value that represents all districts.  Either create an extra district value that comes into Tableau with no data (and you can create a calc to load it up with the sum of all districts) or do that aggregation at the time you create your data source.

                 

                Because your multi-district sheet has districts by color, you'll need to handle the total as an extra district value within the data itself.

                 

                To me, it's far easier to swap sheets.

                 

                Hack up some equivalence of what you are looking to do in Superstore.  Use Regions as the  equivalence of your districts, for example.


                Or anonymize your date as demonstrated in the video linked here:

                 

                 

                Video demonstrates how to anonymize your workbook/data

                • 5. Re: Show total when not filtered otherwise show individual values.
                  Yul Beauchamps

                  I attached what I hope is a correctly extracted workbook file using superstore as the data source.  The way I have it setup in this case isn't how I'd like it but it's best method (comprimise) I could come up with.

                   

                  Cheers,

                  • 6. Re: Show total when not filtered otherwise show individual values.
                    Joe Oppelt

                    In the attached I took your sheet and broke it into two.

                     

                    I created the two calcs I suggested earlier and displayed them in the title of "Rollup".  The Region filter is applied to both Rollup and By-Regions so that you have the same selections from one to the other.

                     

                    When  you are on the Rollup sheet, play with the filter.  You'll see the numbers change as you change selections.

                     

                    Next I made copies of rollup and region to put on a swapping dashboard.  (Dashboard 1.)  I made a filter that compares my two calcs.  One sheet filters for value = Rollup, and the other for value = Regions.  They swap accordingly on the dashboard.


                    Because we can't have titles exposed on swapping sheets, I made Sheet 5 to act as a title sheet.  It's on the dashboard, and the Regions filter also applies to it.

                     

                    So that's a way to display differently-formatted sheets based on the dimension value(s) selected.

                    • 7. Re: Show total when not filtered otherwise show individual values.
                      Yul Beauchamps

                      Hey Joe,

                       

                      Thanks for the great example!  I had to dig around a bit to get how it's set up but it's actually not too bad to follow.  It's a shame it requires the use of three sheets and one dashboard to make it happen.

                       

                      Thanks again for teaching me a couple new tricks. 

                       

                      Cheers,

                      • 8. Re: Show total when not filtered otherwise show individual values.
                        Joe Oppelt

                        The reason for the 3rd sheet is because you can't display titles on swapped sheets.  (Or, swapping doesn't work correctly if titles are displayed.)

                         

                        The reason for the need to swap two sheets is because one display requires compartmentalization by region (defined by Region on the colors shelf) and the other does not.

                         

                        I've been thinking about this some more.  We might have been able to stick with on sheet.  Maybe.  We could have displayed two different calc measures.  The first would get nulled out if the two LOD calcs were equal (and it would would be displayed on the by-region axis) and the other would get nulled out if the two LODs were different (and that would be displayed on the rollup axis.)

                         

                        That would put you back to one sheet.