1 2 Previous Next 16 Replies Latest reply on Nov 21, 2016 1:59 AM by Ben Young

    Can I display total as a whole on a filtered chart?

    Matt Ridley

      Hi All,

       

      I am currently creating some visualisations of healthcare data. I have created my first report which is a bar chart showing the total % figures for all medical schools (below):

      I am also creating the same report for each individual school by filtering the data. However I would love to display the overall total someway on the chart (ideally a line). I have done a (very) rough mock up on paint to give an idea of what I would be after (below). Can anyone work out a way to display both information?

       

       

      Cheers

       

      Matt

        • 1. Re: Can I display total as a whole on a filtered chart?
          shripal gandhi

          Yes you can. In Analysis -->  Totals --> Show Row/Column Grand Totals.

          1 of 1 people found this helpful
          • 2. Re: Can I display total as a whole on a filtered chart?
            Matt Ridley

            Hi Shripal,

             

            Thank you for responding. However, when I add the subtotals (grand total only put one column to the right) it provided me with the below:

             

            Issues I have with this are:

            • The total is not bypassing the filter - instead it is giving me an average percentage across the three years for the filtered school only (not all schools as shown in the first report).
            • I want to show the total for all schools for each year (2013, 2014 and 2015) as opposed to an average across all three.

             

            Many Thanks

             

            Matt

            2 of 2 people found this helpful
            • 3. Re: Can I display total as a whole on a filtered chart?
              shripal gandhi

              Hi Matt,

               

              One way to do this is , create a separate chart for Total. This way the filter will not work on Total chart.

               

              Thanks,

              Shripal

              • 4. Re: Can I display total as a whole on a filtered chart?
                Matt Ridley

                Hi Shripal,

                 

                I have created a separate report (this is the first one posted), however I am interested in displaying the total for all schools on the filtered list as well. I will play around with creating some calculated fields/parameters and will post if I make any progress.

                 

                Cheers

                 

                Matt

                • 5. Re: Can I display total as a whole on a filtered chart?
                  shripal gandhi

                  Hi Matt,

                   

                  Yes. Create two sheets(One with Only Totals) and make it look like one sheet on the dashboard. This way filter will not be applied on Totals sheet.

                   

                  Hope it helps.

                  1 of 1 people found this helpful
                  • 6. Re: Can I display total as a whole on a filtered chart?
                    Ben Young

                    Hi Matt,

                     

                    There is another option you can use. Using Level of Detail (LOD) calculations, you can ignore your filters. The typical format for a LOD calculation is as follows: { Command Dimension: Aggregate Measure } or, in more comprehensible terms: { FIXED Country : Sum(Sales) }. This will return the sum of sales at the country level, regardless of the level of detail of the view. There are other types, but I won't go into all of them here. For more info, here's a link to Tableau's documentation on them: Overview: Level of Detail Expressions

                     

                    In your case, you basically are asking for Tableau to calculate the overall percent of total, regardless of your filter, for percentage of outcomes over the years. You can accomplish this by telling Tableau to calculate at only those levels, using an LOD calculation. Attached is an example for you. Here is the syntax for my calculation:

                     

                    { Fixed [Category], year([Order Date]) : SUM([Number of Records]) }

                     

                    In your case, and not knowing your field names, I'm guessing this is what yours would look like:

                     

                    { Fixed [Outcome], Year : SUM([Number of Records]) }

                     

                    This will give you the number of outcomes per year, regardless of your filter. The LOD works because your first half of the equation doesn't include what you're filtering on (school in your case, State in my example). You can then use a quick table calculation on Pane (Across) to recalculate the percentage for each outcome in each year. Then, using a dual axis chart, you'll be able to overlay your graph.

                     

                    You'll have to be careful with labels to make sure your charts are still readable. I'll leave that to you, but I'm happy to help out if needed.

                     

                    Hope this helps!

                     

                    --Ben

                    1 of 1 people found this helpful
                    • 7. Re: Can I display total as a whole on a filtered chart?
                      Ben Young

                      Here's a quick snapshot for you:

                      • 8. Re: Can I display total as a whole on a filtered chart?
                        Matt Ridley

                        Hi Ben,

                         

                        This looks like it will work perfectly - thanks. Unfortunately I have had to start working on something else now but should have capacity to finish the School analysis Monday. I will make sure to let you know if it works!


                        Regards

                         

                        Matt

                        • 9. Re: Can I display total as a whole on a filtered chart?
                          Ben Young

                          Sounds great! And if it doesn't work, just post back here and I'm happy to help out.

                           

                          --Ben   

                          1 of 1 people found this helpful
                          • 10. Re: Can I display total as a whole on a filtered chart?
                            Matt Ridley

                            Hi Ben,

                             

                            I have tried the calculation you suggested as written here:

                             

                             

                            This has allowed me to get the lines on the graph but all add to 100%. This is most likely due to my unfamiliarity with LOD calculations so would love to hear any suggestions you have.

                             

                            These are the correct figures:

                             

                            These are the figures the calculation is generating (the grey line is all schools):

                             

                            • 11. Re: Can I display total as a whole on a filtered chart?
                              Ben Young

                              Hi Matt,

                               

                              There may be a couple things going on. First, you're right with the LOD expressions; your formula needs a little tweaking. I think there may be another issue with the table calculation (% of total) as well, but let's leave that be for now.

                               

                              For LOD expressions, the idea is that you can force the calculation to operate on a specific level of detail driven by your calculation rather than the view. A simple example is if you want to compare total sales to state sales in the same view. By dragging state out onto the view, Tableau automatically divides up your sales for each state. A LOD calculation tells Tableau to ignore the state field and sum up all the sales.

                               

                              In your case, I think what you're going for is showing a percent of total for each outcome for the entire data set, overlaid on a view that shows outcomes for an individual school. This lets you do a direct comparison between the individual (school) level and the aggregate (overall) level. Because of this, you need to make sure your LOD expression tells Tableau not to calculate at the school level, but at the total level.

                               

                              To do this, I would change your LOD calculation to the following: { FIXED [Outcome], [Year] : SUM([Number of Records] }. This tells Tableau to sum up the number of records at each outcome, in each year. It doesn't matter if you drag school, country, postcode, etc. up onto the view, it will always return the same number: sum of records for each outcome in each year. Using this calculation, you'll be able to then create a % of Total using Quick Table Calculations.

                               

                              With that Quick Table Calc, the second thing I think might be going wrong is the scope of the Table Calculation. It needs to be on Pane (Across) rather than Table (Down). This will have it calculate % of total for each outcome (pane is visually defined by the grey column bars). If you have it run Table (Down) (which is often the default), it will always be 100% because you're only summing one number in the bar chart. That being said, you may already have this taken care of and the LOD was the main culprit. Just a thought.

                               

                              Let me know if this helps.

                               

                              --Ben

                              1 of 1 people found this helpful
                              • 12. Re: Can I display total as a whole on a filtered chart?
                                Ben Young

                                Hi Matt, hadn't heard back from you on this. How are things going? Just wanted to check in.

                                • 13. Re: Can I display total as a whole on a filtered chart?
                                  Matt Ridley

                                  Hi Ben,

                                   

                                  We are getting really close now! Thank you for all the help. Just one issue remains.

                                   

                                  Firstly my % of total is on 'Outcome' as we are looking at how the years are split up by outcome. In the image below you can see that 2013 was made up of 50.77% Outcome 1, 12,31% Outcome 2 etc.

                                   

                                   

                                  My final issue arises through the fact that, as outcomes like 7.1, 7.3 and 7.4 only show one column (as there is no data for 2014 and 2015) in the School of Obstetrics and Gynaecology, this is also applying to the overall total. However there is data within these outcomes for overall which are not showing on the above sheet. Due to these not appearing it is throwing out the overall total a little bit.

                                   

                                  I may have worded this terribly so please let me know if you would like me to explain further.

                                   

                                  Thanks Matt

                                  • 14. Re: Can I display total as a whole on a filtered chart?
                                    Ben Young

                                    Your explanation makes sense. I'm glad we're getting close! This last step is a tricky one, and it has to do with how Tableau interprets and draws data: Tableau will only create columns/draw marks for places where it has data. This means in outcome 1,2,3,etc., where it has data for all three years, it will have a column for each. But, for 7.1, 7.3, and 7.4 Tableau has no data telling its engine that you would like to see the other years, so it cannot write in a mark for them on the visualization. A common place people run into this is wanting to see sales by day, but not having regular, daily sales.

                                     

                                    A way to work around this is to make sure your data has values for every timeframe you're looking for, even if it means putting in data that has a 0 value. I've attached a workbook that hopefully will help demonstrate this. It will mean that instead of number of records, you'll have to count only values that aren't zero.

                                     

                                    Let me know if this helps.

                                    1 of 1 people found this helpful
                                    1 2 Previous Next