8 Replies Latest reply on Jan 20, 2016 11:18 PM by Brian Moore

    Show filtered % data source total

    Brian Moore

      I have a dashboard that is designed to be interactive for the end-user. Given that users will interact with a number of filters on different dimensions, I'd like to use a worksheet visualization as a sort of legend for what percent of the total data in the date range is currently in the view of the dashboard.

       

      Many analytics user interfaces achieve this type of visual as a nice reference for when creating subsets or segments. Here's an example of what it looks like in Google Analytics Custom Segments builder:

       

      Screen Shot 2016-01-19 at 4.52.08 PM.png

       

      I'm going back and forth with LOD expressions to exclude the various dimension filters on the dashboard, or to TOTAL() or other table calc functions and I'm not getting anywhere.

       

      Any guidance would be greatly appreciated!

       

      Brian

        • 1. Re: Show filtered % data source total
          Adam Crahen

          Hi Brian-

           

          All this is possible. I did a number of things to make this work the way I think you desired.

           

          2016-01-19_22-03-17.png

           

          I created three parameters:

          • Start Date
          • End Date
          • Metric

           

          I created a Date Set and In/Out Dimension.  This determines if the date on each record is between the start/end date parameters.  The dimension is used for color and the table calc.

           

          I created a Metric Measure to swap between the metric 1 and 2 measures. 

          CASE [Metric]

          WHEN 1 THEN [Metric 1]

          WHEN 2 THEN [Metric 2]

          END

           

          This Metric Measure can be used to do the table calc.  I did an advanced compute on it because I had two dimensions in the view.

           

          I also created the donut chart you were after, but you will now see at least three comments telling you a bullet chart is better! 

           

          The real key to all this was ditching the date filter you had applied to the entire data source.  Instead, I used the In/Out dimension as the date filter for your line chart.

           

          9.1.3 workbook attached.  Happy playing.

          2 of 2 people found this helpful
          • 2. Re: Show filtered % data source total
            Rajeev Pandey

            This is really nice .

            I am very much interested to know how you made the below blue background by keeping the White tick mark. It looks so pretty and elegant.

            • 3. Re: Show filtered % data source total
              Adam Crahen

              I can't take any credit for that one.  I am just using a Mac.

              • 4. Re: Show filtered % data source total
                Rajeev Pandey

                So its Because of MAC OS. I thought we can implement the same in tableau

                • 5. Re: Show filtered % data source total
                  Brian Moore

                  HI Adam Crahen

                   

                  Thanks for your detailed reply! However, I'm not sure if I clearly explained my intention initially, but now I can utilize your example to clarify my thinking:

                   

                  See below where I have the entire date range selected, but when I deselect Canada, I should see a distinction that some of the value of SUM([Metric 2]) is not in view, since Canada is now filtered out.

                   

                  I'm envisioning end users selecting a subset of the date range from the data source, but I want whatever subset selected to equate to 100% of data source total. In that sense, I'm seeking to expose the %total chart to the date range filter, but exclude it from the Country & Category filters.  In my head, this seems like the perfect opportunity to use a LOD function like the following:

                   

                  { EXCLUDE [Country], [Category] : SUM([Metric 1]) }

                   

                  And this would be the metric used in the denominator for % total in view, while SUM([Metric 1]) is exposed to the filters on those two dimensions.

                   

                  Again, thanks for your willingness to help! And I was actually thinking a bullet chart in my proprietary workbook

                   

                  Sincerely,
                  Brian

                  • 6. Re: Show filtered % data source total
                    Adam Crahen

                    Hey Brian-

                     

                    Yeah I designed that with the notion you were only looking for in/out of date range.  If you are looking to incorporate other dimensions, the LOD is a good route.

                    • 7. Re: Show filtered % data source total
                      Adam Crahen

                      Since you aren't set on the donut chart, check these out to see if this gets you closer.  Did the LOD, a bullet and bar view of the data in the view.

                       

                      This is quick and dirty, but you could cleanup the formatting.

                       

                      2016-01-20_14-46-18.png

                       

                      Here is another if you swap the measures in the column shelf:

                      2016-01-20_14-56-06.png

                      1 of 1 people found this helpful
                      • 8. Re: Show filtered % data source total
                        Brian Moore

                        Adam

                         

                        This was enough to get me down the right path. I merely added the Date filter to context in the view and got the desired level of filtering using the LOD calculated fields. Thanks so much for all your help!

                         

                        Cheers,

                        Brian