6 Replies Latest reply on Dec 30, 2016 3:24 PM by Jonathan Drummey

    Using subtotals and totals as measures?

    Alexis Sannoh

      Hi all,

       

      I am not quite sure that this question has been answered or not. I have read through the different forums relevant to this post but I am still quite confused. I am new to tableau and I have two local agencies that I am working with that have clinic level data as a subcategory, broken down by months. I want to be able to replicate the subtotals and both the row/column grand totals embedded in tableau and use them as measures to visualize the data in different ways. Is this possible? If so, how can I go about it? Is it best to use calculated field formulas? OR would be easier to just send the data back to excel and compute the sub/grand totals from there?

       

      Any guidance you all have would be greatly appreciated.

       

      I have attached the workbook example I created of what I would like to replicate.

       

      Jonathan Drummey I have read a lot of discussions on this topic and you were in most of them, would you mind taking a look at this for me as well, please?

       

      Thanks

        • 1. Re: Using subtotals and totals as measures?
          Maciek La

          Hi Alexis,

          it would be great if you could provide at least mockup of the dashboard you are trying to produce.

          You can create calculated field which will calculate total on different levels using LOD expression. Those can be later used as filters / sorters.

          • 2. Re: Using subtotals and totals as measures?
            Matthew Risley

            as Maciek La stated, you can certainly use calculated fields to get your answer! This is probably the most healthy way as you can keep the data Tableau-friendly and in one place.

             

            We will certainly help you if you are struggling with the calculations!

             

            However, if you are bashing your head against your keyboard and not able to get the correct result, there's nothing wrong with making another table outside of Tableau with the aggregations you need in order to get the result you're looking for.

             

            Hope that helps!

            --Matt

            • 3. Re: Using subtotals and totals as measures?
              Jonathan Drummey

              Hi Alexis,

               

              I suspect you're approaching Tableau with the mental model of a tool like Excel (the most commonly used BI application) and your confusion is coming from Tableau having a different mental model than you're used to. In Excel we'll start out with some data, then write some formulas such as SUM(A2:A15) reference the cells of our data to create our totals in some other cells, then maybe do some more cell references to manipulate that, and so on until we get ht results we want.

               

              Tableau works very differently, there are the two key differences that I've identified:

               

              1) Unlike Excel's cell-based model Tableau approaches data more like databases do where we define the dimension(s) we want to use to slice/group/partition our data by and then Tableau automatically aggregates the measures to the appropriate level based on the dimensions in the view. As we add and remove pills from our view Tableau re-queries the data as necessary so we don't have to rewrite formulas or queries when we want to change an aggregation level.

               

              2) In Excel our mental model is that we have separate worksheets (or areas of worksheets) and then manipulate them serially, whereas Tableau has an "all at once" model where the marks, calculated fields totals, subtotals, trend lines, reference lines, etc. on a given worksheet are all computed in a single (large) operation. So in Tableau we presently don't build a calculation in one worksheet and then use the results of that in another, instead we effectively define the aggregation level for the desired viz (via the dimensions in the view) and then start adding measures into the view. Tableau has a variety of calculations that can work at different levels of granularity if we need to, for example quick table calculations can do things like % of total in just a couple of clicks.

               

              The combination of these two elements hides complexity and makes Tableau faster to work with than most other tools right up until the point where Tableau's controls and feedback mechanisms don't give us the information we need to do our work, and then we're stuck trying to figure out how to match up our mental models with how Tableau actually works.

               

              I'll give you a couple of quick examples based on the data you published. The data has one record per month & clinic, and the Sheet 1 view has Month, Clinic, and Local Agency in the view. So it's showing the grain of the data. the Subtotal on Local Agency is aggregating to the level of Local Agency & Month, we can get those same results in a separate worksheet just by removing Clinic as a Dimension from the view:

               

              Screen Shot 2016-12-27 at 11.09.11 AM.png

               

              The Grand Total on Columns is effectively aggregating to the level of Month, so we can get that by removing Local Agency from the view:

               

              Screen Shot 2016-12-27 at 11.10.27 AM.png

               

              So Tableau is automatically aggregating the data based on the dimensions in the view...change the dimensions, change the view. Just this alone can get us a long ways in Tableau.

               

              Going back to the original view, if I want to do a comparison of the monthly values to the average at the local agency I could do that by building calculations for the text table, but text tables aren't great for that kind of thing. With a few clicks and no custom calculations and using an Average reference line I built this view:

               

              Screen Shot 2016-12-27 at 11.14.04 AM.png

               

              So things like quick table calculations, reference lines and using alternative visualizations from text tables can get us even further in answering the questions we have for our data without ever having to leave our mouse. From there we have Tableau's wealth of functions and calculations.

               

              Hopefully that helps you get going, if you need more assistance from here I concur with Maciek and Matthew that giving us a better idea of what your goal is would be the next step. Sometimes what are very complicated operations in other BI tools can be done in Tableau in a couple of clicks, sometimes they are also complicated in Tableau.

               

              Cheers,

               

              Jonathan

              2 of 2 people found this helpful
              • 4. Re: Using subtotals and totals as measures?
                Alexis Sannoh

                Hi guys,

                 

                I am not quite sure how to respond to all but I am really appreciative of you all responding to my post and providing me with new perspectives.

                 

                Jonathan, thanks for the thorough explanation of some of the differences between excel and tableau...its something I have been definitely struggling to wrap my head around.

                 

                As Maciek La suggested, I have attached a mock up of my desired dashboard...its just a quick compilation in word document of the graphs I want to be able to view. I would really like to compare the actual numbers for the clinics by month to the agency totals per month, side by side in a bar graph. The agency totals in this case refer to the subtotals and what I refer to in the graphs as the state total is actually the grand total. I thought I could create calculated fields for the agency totals and the state total, but I am really struggling with what the calculations would look like.

                 

                If this does not make sense to you, I can elaborate more.

                 

                Best,

                • 5. Re: Using subtotals and totals as measures?
                  Alexis Sannoh

                  Hi,

                   

                  I don't know if you had the chance to look at the mock up dashboard I sent yesterday but I think the workbook I have attached it much clearer. I just need help with the formula to be able to replicate the subtotals and grand totals, which I have included as the measures agency totals and state totals, respectively.

                   

                  Best,

                  • 6. Re: Using subtotals and totals as measures?
                    Jonathan Drummey

                    Hi Alexis,

                     

                    I can match the Agency totals with a Level of Detail (LOD) expression like {EXCLUDE [Clinic]: SUM([Actual Amount])}, but it looks like the State totals are including values that aren't in the data? How are they computed?

                     

                    Jonathan