1 2 Previous Next 21 Replies Latest reply on Oct 30, 2014 9:26 AM by Henry Carlson

    Total Bar Does Not Match Rows in View

    Rachel Gadd

      I have created a fairly complex view that allows business users to rank products by a number of different e-commerce metrics (eg, sales, units sold, page views) and filter for date/date range, type of product, product hierarchy, etc.. There is also a filter that allows the user to define the number of records to display. For example, they can ask to see the top 20 products by page views for Sept 1 - 7th that are under the category of jewelry. I constructed the 'Number of Products' filter by creating a calculated field 'Rank' defined as RUNNING_SUM(MIN([Number of Records])) and then displaying the quick filter, which then allows the user to define a range.

       

      The issue I'm having is that the default Total row seems to be calculated prior to the 'Number of Products' filter, such that the total displayed is all applicable records that match the filter conditions. How do I set up the Total bar to understand that I would only like to sum the rows that are in the view itself.

       

      Thanks!

        • 1. Re: Total Bar Does Not Match Rows in View
          Leigh Fonseca

          I've seen Rachel's dashboard and it is remarkably complex and yet simple for the user.  That said, this issue can be easily seen using Tableau's default data set and hopefully the attached workbook will enable someone to help us out with this.  Basically, it appears that the column grand totals are the total for the entire period's data not the Top 20 records selected.  Is there a way to accurately show the Totals for only those records being viewed?

           

          grand-total-too-grand.png

           

          As you can see I don't have $6M in sales showing but there are a total of $6M in sales if I show all records, not just the Top 20.

           

          https://public.tableausoftware.com/workbooks/Forums-GrandTotalIncorrect

           

          Thank in advance!  Would love to solve this

           

          Cheers,
          Leigh

          1 of 1 people found this helpful
          • 3. Re: Total Bar Does Not Match Rows in View
            Jonathan Drummey

            I didn't cover this exact situation in that series of posts that Shawn linked to, but I did cover one solution. Tableau Grand Totals have a separate computational path from the detail rows, and in Tableau's order of operations the detail rows get filtered by table calcs and as you've seen the Grand Total rows do not.

             

            One workaround is covered in part 3 of the series, where you'd use a duplicated data source and build your own totals. Another would be to move the Top N filter into the measures themselves, so even the Grand Total computations would  take the filter into account. (You might need to do some extra work in the calcs to deal with how Tableau changes the level of detail in the GT computation).

             

            I haven't tested this particular scenario with Tableau v8.1's new multipass grand totals, I'll try it out and get back to you. One question in the interim, are you using any calculated fields that explicitly reference blended data sources or table calcs in your view?

            • 4. Re: Total Bar Does Not Match Rows in View
              Jonathan Drummey

              Just tested multi pass grand totals in 8.1 beta 6 and the behavior from earlier versions of Tableau is retained - in other words, the new grand totals are still computed separately from the detail rows, so table calc filters don't apply. The reason why I'd asked about table calcs or blended calculated fields is that they aren't supported by the multi pass grand totals in 8.1.

               

              That leaves you with the two options I described, or a third being to use two separate worksheets (one for detail, one for GT) on a dashboard.

              • 5. Re: Re: Total Bar Does Not Match Rows in View
                Ville Tyrväinen

                This might not solve every issue but can't you just use top filter?Dashboar1.jpg

                • 6. Re: Re: Total Bar Does Not Match Rows in View
                  Jonathan Drummey

                  Hi Ville,

                   

                  A Top filter is an excellent idea, and you're right, it might work.

                   

                  I'd considered a top filter and (perhaps too hastily) discounted it, here's why: In describing the viz, Rachel wrote "...and filter for date/date range, type of product, product hierarchy, etc." In Tableau, Top & Condition filters have a precedence such that they are applied before other filters (technically via a subquery that is joined in). So the Top N customers would be the Top N across the entire data set, and if a customer was filtered out because of a product selection or date range then only N-1 customers would be shown, instead of N, and usually people always want to see N customers.


                  Context filters occur even sooner in the pipeline, so theoretically one could make all the Quick Filters into Context Filters, then have the Top N come after that, however:


                  a) I wasn't sure whether any aggregate measures had been used for filters (aggregates cannot be used as context filters, only dimensions).

                  b) Context filters can have huge performance impacts, since Tableau has to build the context in order to then apply other filters to it. Tableau's advice has been that in general context filters are likely to improve performance when the context filter returns only 1/10th the original data or less, more than that and they can be detrimental to performance. Given the description of the view by Rachel and the demo by Leigh I was guessing that this was not the case, so I didn't suggest the context filters.

                   

                  Cheers,

                   

                  Jonathan

                  • 7. Re: Re: Total Bar Does Not Match Rows in View
                    Matt Lutton

                    I'm not sure what you meant by:

                     

                    ...move the Top N filter into the measures themselves, so even the Grand Total computations would  take the filter into account....

                     

                    I tried building a separate sheet solution but ended up with the same results.  I am not sure how to get the TOP N filter to apply to the separate grand total sheet, and I've never worked on a case like this.  This is an interesting case and it does seem difficult to get the grand total to show what you want (unless you use the Top N Filter Ville described).

                    • 8. Re: Re: Re: Total Bar Does Not Match Rows in View
                      Ville Tyrväinen

                      Hi Jonathan

                       

                      Thanks for clarifying things. I tested same with context filters, but it should be tested with bigger amount of data.

                       

                      Ville

                      • 9. Re: Re: Re: Total Bar Does Not Match Rows in View
                        Jonathan Drummey

                        Hi Matthew,

                         

                        I was starting to write up how to do this (feeling a little short on time today), it was faster to put an example together, see the attached for an answer to your first question on moving the Top N filter into the measure calculations. I used the duplicate dimension technique from Customizing Grand Totals – Part 2 | Drawing with Numbers. Note that this uses nested table calcs, so be careful with the Compute Usings.

                         

                        As for the second question re: a separate worksheet, the grand total worksheet would have to be built with the right level of detail to return accurate results, then use table calcs to collapse that back down into a single row. It's probably more work to do than the single worksheet.

                         

                        Jonathan

                        • 10. Re: Re: Re: Total Bar Does Not Match Rows in View
                          Matt Lutton

                          Jonathan:  As always, thanks for taking the time to teach others.  I really appreciate it.  And I apologize if I end up asking you the same question more than once!

                          • 11. Re: Re: Re: Total Bar Does Not Match Rows in View
                            Matt Lutton

                            I noticed you had hard coded the calcs, like:

                            IF FIRST()==0 THEN WINDOW_SUM(IF [Rank] <= 20 THEN SUM([Profit]) END) END

                             

                            It would be dynamic based on the Rank filter, using:

                            IF FIRST()==0 THEN WINDOW_SUM(IF [Rank] <= [Rank] THEN SUM([Profit]) END) END

                             

                            Very educational stuff for me, I really appreciate it!    

                            • 12. Re: Total Bar Does Not Match Rows in View
                              Rachel Gadd

                              Hi all,

                               

                              Thank you for the in depth responses! I've already picked up a couple of new tricks in the process... especially the 'Rank for Display' field that gets rid of the rank showing up in the total bar! Very cool.

                               

                              I've been through the suggestions but so far have not been able to find a working solution.

                               

                              As for the suggestion of using the Top N filter, this doesn't seem to work with all of the additional filters on the report. I'm encountering the same scoping issues. We also investigated modifying the context filters, but the problem with that seems to be that there is no predefined context to nail down. In addition, we're working with a very large data set so it's likely that the performance hit wouldn't be acceptable for end users even if it did work.

                               

                              It looks like the only possible solution will be to build out additional sheets to serve as workaround total bars. I'm anticipating that being a tricky development process though.

                               

                              Again, thanks for all of your help. I've learned a lot!

                               

                              Best,

                              Rachel

                              • 13. Re: Total Bar Does Not Match Rows in View
                                Ville Tyrväinen

                                What is "very large"? millions rows or more?

                                • 14. Re: Total Bar Does Not Match Rows in View
                                  Leigh Fonseca

                                  Thank you everyone for your recommendations.  Rachel and I stepped through each one of them to see if we could leverage your suggestions and learned several great new tricks.  Unfortunately, the complexity of the report (aggregated measures and multiple filter types [date range decoding parameter selection, Boolean set determination, dimension and calculated field dimension filters]) prevented these from working.  The date filter conflicted with the Top N, the users want to select a Range so Top is difficult (they wanted to be able to display 50-100, not just Top 100), etc.  The context filter suggestion may worked it if wasn't for the multiple dimensions that were filter options to go with the range of products.  There's really no context we could nail down because they want Top 20 by Department A, SubDept B, SubSubDept C, on a given date and then to change the department, date ranges and rank positions displayed.  Rachel really is dealing with a high degree of complexity and keeping it simple can be a challenge.

                                   

                                  Your responses were great though and we really appreciated the tips we picked up.  Thanks for all your very timely feedback!

                                  1 2 Previous Next