1 2 3 Previous Next 31 Replies Latest reply on Apr 30, 2014 11:43 AM by Matt Lutton

    Calculation: displaying total amount, and percentage based on filter

    ann.stolzman

      Hi all,

      I'm trying to get a quick crosstab going here, and I'm stymied by what is probably something really simple.

       

      I want to display the total dollars booked (by country), and then show the percentage of those dollars that are a specific product line.  The product line is driven by a filter, but then total bookings is affected by that filter.  Can I have it suppressed from the filter affecting it?

       

      It would look something like this (I don't have to show the specific product bookings, but I put it in for information's sake)

       

      CountryTotal BookingsSpecific Product BookingsPercentage of Total Bookings
      Country 1500001000020.0%
      Country 21000002500025.0%
      Country 34500006000013.3%

       

      Is this even possible?

       

      Thanks in advance!

      -Ann-

        • 1. Re: Calculation: displaying total amount, and percentage based on filter
          Matt Lutton

          Using a Table Calculation filter will allow you to filter without impact the percentage calculation--here's an example:

          A Jedi (Filter and Table Calc) Trick | Tableau Software

          1 of 1 people found this helpful
          • 2. Re: Calculation: displaying total amount, and percentage based on filter
            ann.stolzman

            Well, that definitely helps.  Now I have something that looks like this (I've blocked out the country names, and the business entity names).

             

            Can I group the business entity names, so that I just have one percentage?  I do have the Business Entity as a set but I can't do the calculation on the set because it says it's being called with a boolean.  I can show the total bookings as a subtotal of the country, but I want that side by side (I removed it).

             

            I'm really struggling with this, and I shouldn't be.  I'm sure it's something simple, and I'm still tinkering.... any other guidance would be helpful.  Like I said - the goal is the format above, and I can't quite get it there with the "Jedi" trick suggested.

             

            -Ann-

            Category.jpg

            • 3. Re: Calculation: displaying total amount, and percentage based on filter
              Matt Lutton

              I'm not sure what you are looking for, in terms of an end result.  If you can post a sample packaged workbook, along with a mock up of your intended output, I'll be happy to take a look.  You can use a sample dataset provided with Tableau to create a similar view, or pull a subset of your records into Excel, randomize them, and pull that into Tableau as an extract for us to use within the thread.  I'd guess that once you post a workbook, you'll have a solution very quickly.

              • 4. Re: Calculation: displaying total amount, and percentage based on filter
                ann.stolzman

                What I want is a layout just like what I posted in the original message.  The column for "Specific Product Bookings" are going to be all the same product, because I'm analyzing a subset of a large data set.  I can't really get a packaged workbook together, because I'm pulling from a live database, but I'll try to mock something together in Excel as a datasource that I can post here.

                • 5. Re: Calculation: displaying total amount, and percentage based on filter
                  Matt Lutton

                  Like I said, you can copy and paste a subset of records into Excel (or create a very simple data set that matches the structure and granularity of your actual data), pull that into Tableau as an extract, and post the work you've been able to complete here, along with a mock up of what you want for an end result (including the values you want displayed, etc).  Alternatively, you can try to mock up a similar example using the Superstore Sales dataset provided with Tableau.  Just make sure the granularity of the data and the end goal are similar.

                   

                  Its very difficult to help without this, as we cannot see the data or calculations--and I'm still not clear on your end goal, based on the original table and the screenshot you posted; mostly, because the first table does not include business entities.  This is where most of my confusion sprouts from.

                   

                  Once you get some sample data together, and can explain the goal clearly (no offense--I just don't understand the end goal here), I'm certain a solution can be applied.

                   

                  I originally thought the goal was to filter without impacting the percentage results--the Table Calc filter method allows us to do this, but perhaps I misunderstood what you are wanting to do.

                  • 6. Re: Calculation: displaying total amount, and percentage based on filter
                    caitlin.donaldson

                    Hi Ann!  As I understand your question, you want % of bookings by country, not broken down by business entity.  Is there a reason business entity is in your view?  Are you trying to get an average % of bookings at a business entity level of detail?  If so, there could be ways to build a calculated field to get what you need, depending on how your original data is structured.

                    • 7. Re: Calculation: displaying total amount, and percentage based on filter
                      ann.stolzman

                      Ok, I've attached a workbook above with mock data. 

                       

                      Here's what I need to see as the end result:

                      Country (Name of the country)  |  % of Total Bookings for the chosen Business Entities (the calculation is basically the sum of the bookings for the chosen business entities over the total bookings for that country) |  $ of Bookings for the chosen Business Entities (the sum of the bookings for the chosen business entities)

                       

                      Here's the rub: I need those Business Entities to be grouped as one instead of individual items.  Heck, I don't even need to see them on the dashboard at all.  I've included the subtotals ONLY for illustrative purposes that I've got the percentage calculations down, but I can't find the way to that last mile where I can remove the entity and have the calculation stand.  The minute I remove it, the discrete sum(actual bookings) fails.  If I hide the header, the data stays, but they're still on individual lines (not what I want)

                       

                      On my final dashboard I have filters that drive quarter, Business Entity (there are two we're focusing on, and we want to have users show one, the other, or both), country name, and Business Sub Entity (BSE).

                       

                      This item would likely be affected by all of these, but I can probably drop BSE if I need to. 

                       

                      Is that clearer?

                      • 8. Re: Calculation: displaying total amount, and percentage based on filter
                        Matt Lutton

                        Yes, somewhat.  It would be helpful to know what you are expecting as an end result, including what values you expect to see, and at what level of detail, but I'm not sure if you know what values you are expecting.  I assume you are wanting the combination of the two business entity percentages at the country level?  So, for country 6, you'd want to see 14.26%?  What do you want to see for the Actual Bookings, then?  For Country 6, would it be the sum of 43, 403 and 79,227 or the $860,144 value that is showing for your Total?

                         

                        This is what I meant by a mock up of your intended results--it helps to know what the expected output should be.

                        • 9. Re: Calculation: displaying total amount, and percentage based on filter
                          ann.stolzman

                          Yes, for Country 6, I would want to see the 14.26% (the combined bookings for the two entities divided by total bookings for that country), and I would want to see the total bookings value (the 860,144).

                          • 10. Re: Calculation: displaying total amount, and percentage based on filter
                            Matt Lutton

                            I'm not sure why this is so difficult, but if we could aggregate the percent of total like we can the Actual Bookings, we could easily arrive at a solution.  I just cannot seem to find a way to total the percentages at the country level; no matter what I try to aggregate the percentage values, I get 100% for each country.

                             

                            Joshua Milligan, Noah Salvaterra, or anyone else  :  Any way you can drop a hint without providing the answer?  There's got to be something I am missing here!

                            • 11. Re: Calculation: displaying total amount, and percentage based on filter
                              caitlin.donaldson

                              I tried it out and, for some reason, Country 101 is throwing off all the numbers.  I am not able to figure out why.  I was able to get this once I filtered out Country 101.

                               

                              2014_04_16 Bookings Screenshot.JPG

                               

                              The last column's calculation is SUM([Actual Bookings])/TOTAL(SUM([Actual Bookings])).  The numbers aren't right since Country 101 is not included, but hopefully this helps as a start!

                              • 12. Re: Calculation: displaying total amount, and percentage based on filter
                                Matt Lutton

                                Yeah, I don't believe that is accurate either.  Country 101 shouldn't have any impact on this calculation, as far as I can tell.  Plus, the business entities do need to be considered.

                                • 13. Re: Calculation: displaying total amount, and percentage based on filter
                                  caitlin.donaldson

                                  I know.  I'm just trying to provide it to move the discussion forward.

                                  • 14. Re: Calculation: displaying total amount, and percentage based on filter
                                    Joshua Milligan

                                    Matthew,

                                     

                                    You might think about trying one of these:

                                    1. Nested table calculations (change the existing one from a quick table calc to a custom one where you can then embed it in another), specifically you might think of a running total.
                                    2. Data blending using a copy of the data source is another way to get aggregates that are not changed by filters.  That would avoid some of the table calc complexities -- you could get the % with an aggregate level calculation and then use a much simpler table calc to WINDOW_SUM those.

                                     

                                    Hope that helps!

                                     

                                    Regards,

                                    Joshua

                                    1 2 3 Previous Next