13 Replies Latest reply on Oct 10, 2019 10:01 AM by Kaz Shakir

    Best way to calculate change in % of total over time?

    Robert Hyatt

      At my job a big metric for us is category and market share.  Which essentially is just the % of total our product makes up within a category (think nike running shoes in the running shoes category).


      Calculating the % of total has been easy enough and I've found at least 2 simple ways to do that in tableau.  In the attached workbook example I calculate % of total by bringing in the quantity field and choosing the 'quick table calculation>% of total' and I also played around with calculating it with a calculated field (sum([Quantity]) / TOTAL(SUM([Quantity]))).  Both of those seem to be working just fine.


      The issue I've been struggling with is calculating the change in that number year over year.  This feels like it should be easy to do, but I've struggled and eventually gotten it to work with both a table calc and with an LOD.  But neither has been consistent at all for me.  Seems like half time I create a new view using different dimensions, my change calc will just spit out totally incorrect numbers.


      For example, in my attached example the 'share change' field IS working (2016 share is 11.6%, 2017 share is 9.8%, so change is -1.8%).  But every time I add/remove dimensions to make a different view then I get into having to keep adjusting the table calc.  It's so frustrating.  When I came across fixed LOD's I thought I had the solution, but even with those it seems like they only work when your dimension is at a higher level than what dimension you have specified in the calculation?  Not exactly sure on that.


      So what I'm hoping to find is an easier way to do this seemingly simple math. I feel like I must be making this way harder than it actually is?  All I want to do is subtract 2 percentages year over year.  Is there an easy way to do this that adjusts easily to addition/subtraction of dimensions for alternate views?  Help!!!


        • 1. Re: Best way to calculate change in % of total over time?
          Kaz Shakir


          I'm not sure I understand the issue you are facing.  In your example, if I use an LOD to calculate the % Of Total QTY, and then use a table calc to determine the change year over year, I see the following viz, which has the values as you indicated: 11.6% in 2016, vs. 9.8% in 2017, and therefore, the year over year is -1.8%.


          And then, if I remove the Category dimension, the numbers change, but they are still correct:


          Or, if instead of removing category, I added subcategory, the numbers, again change, but are still correct:


          And, just FYI, the LOD calc I used for the % Of Total QTY is as follows:


          Can you please provide a little more detail about the issue you are experiencing, so that we can try to replicate it, and offer you a solution?


          • 2. Re: Best way to calculate change in % of total over time?
            Robert Hyatt

            Your answer looks intriguing, maybe that is headed down the right path for what I need.


            I tried it in my actual workbook and it's still not working for me though.  It's producing the result of 0.0% for all rows.  Below is the code I tried, with the only real difference being that I just have a 'year' date field (2017, 2018, 2019, etc).  Not sure if it's something to do with that or not?


            I wish I could post the actual workbook I'm using, but I can't because it's sensitive business data.


            SUM([Item Units])/SUM({ FIXED [Year]: SUM([Item Units])})

            • 3. Re: Best way to calculate change in % of total over time?
              Robert Hyatt

              I actually can't even get your LOD to work in the superstore example workbook I gave.  Can you attach the workbook you did?


              To be more specific, Part of what I'm having to do is add more measures to the columns shelf.  When I do that, my prior 2 calculations still calculate share correctly, but the LOD now doesn't?  But I can't get the % difference to work even on the original sheet. 


              I attached an example (sheet3 is the new sheet with region added in on columns shelf)

              • 4. Re: Best way to calculate change in % of total over time?
                Subodh Gupta

                Remove the Region from your Columns.

                Change the AGG(Share LOD) to go across and just difference, not percentage difference.

                See attached.

                • 5. Re: Best way to calculate change in % of total over time?
                  Robert Hyatt

                  Ok that works when I take out Region, but that goes back to what my original issue is.  I was hoping there was a function that would auto adjust to what dimensions I bring in or take out for either columns or rows.


                  Actually if I do bring in Region I'm unable to get the calculation to work at all even if I adjust it (tried adding the Region to the LOD calc and tried adjusting the table calc).  So that's my root issue, I can't seem to find anything dynamic that will work with different dimensions.


                  So it seems like you're saying if I want to add a dimension (like region) then I have to manually adjust my LOD/calculation to try to make it compute right, is that correct?  Would INCLUDE work for what I'm trying to do?  Maybe something else?

                  • 6. Re: Best way to calculate change in % of total over time?
                    Subodh Gupta

                    Table calc (in my opinion, I know some people tell me otherwise) always end up imposing some limitations on how you slice and dice the data.

                    I think Kaz's solution is great. But as I see it, it will work as fine as long as you don't slice the column. I'd be happy to learn that I'm wrong.


                    If you add the region to your rows, you'd be fine.

                    That's because in Kaz's solution he is using Fixed across years. So if you slice that, each window is a part of 100% vs a total of 100% as in your original solution.

                    It becomes a little more clear if you export the data into a crosstab:


                    For each region, the column that you originally had, adds up to 100% (yellow). In Kaz's solution, when you add the Region to slice the columns, each region is part of the 100% (in blue).


                    As a side note, Kaz, not saying there is anything wrong with your solution. It works. Just using it to illustrate other issues I have had. I'd glad if you can help further.


                    I'm still learning, I'm hoping there is a better solution.

                    • 7. Re: Best way to calculate change in % of total over time?
                      Ciara Brennan

                      Hi Robert

                      If you have time could you please take this quick survey to provide feedback on your experience with the Tableau Community Forums?

                      ***Tell us about your experience on the Tableau Community Forums***


                      Much appreciated


                      [Program Manager | Tableau Community Forums]

                      • 8. Re: Best way to calculate change in % of total over time?
                        Kaz Shakir


                        Let me respond in a couple of different messages.  First, as you requested, here's the workbook I used for the example I posted earlier.

                        • 9. Re: Best way to calculate change in % of total over time?
                          Kaz Shakir


                          Second, Subodh is correct in his explanation of what's happening.  Something that's important to keep in mind, is that creating an LOD expression does create a new column in your dataset, in the same way that you might think of creating a calculated entry in Excel will work.  Instead, in Tableau you have just created a measure; and a measure will re-calculate everytime you change your visualiztion.


                          Specifically, with the FIXED clause in an LOD, that tells tableau to take no more than the variables listed in the FIXED clause for items to group the data by.  Let's take a simple example to help understand how it works.  Let's create a calculated field like so:

                          And then let's place it into a viz, along side just a simple SUM of the quantity value:

                          As you can see the values are the same.  Remember that the FIXED clause just tells Tableau to take no more than the two variables, Region and Category, into consideration when summing the quantity.  In this case just a straight sum of the quantity returns the same value, because the viz has also grouped the data by region and category.  Now, let's add subcategory to the viz, and see what happens:


                          Now, the values for the two measures (SUM(Quantity) vs. the LOD) are different.  And, you notice that the LOD values are the same as they were in the previous viz.  This is because the LOD stopped the grouping of the data at just Region and Category, while the SUM(Quantity) measure used the grouping of the viz, which also included sub-category.  Now, let's go in the opposite direction, and just show Category only on our viz:

                          That's an odd result.  Why didn't the LOD continue to group the data by Region and Category?  That's because the FIXED clause will group the data by no more categories than Region and Category, but if the viz is using a lower level of grouping, then the LOD will adopt that lower level of grouping.


                          That's a bit of a lengthy explanation, but now let me see if I can relate it back to your situation.  In your case, you are actually wanting the LOD to group at exactly one level of grouping less than the grouping you have included in your viz.  That's a challenging thing to accomplish with an LOD, because how is the LOD supposed to know which of the grouping categories you have included in your viz is the one to leave out of the formula?  Same issue with the table calculations - how can Tableau know which grouping category you want to include or exclude when performing the calculations?


                          An alternative approach to consider, would be to create different worksheets for the most common levels of grouping that you believe your users will want to see; then, on a dashboard, you might be able to use a parameter to allow the user to select a level of grouping and then show the appropriate worksheet based on the selection from the parameter.


                          Hope that helps.


                          • 10. Re: Best way to calculate change in % of total over time?
                            Robert Hyatt

                            Kaz, thank you for the detailed explanation!  Not what I was hoping to hear, but still appreciated nonetheless.  If nothing else, I at least feel better that what I'm failing at here is something a bit complex


                            While working with my actual workbook I just came across yet another totally bizarre case.  It makes me wonder if something is broken here.  I'm hoping maybe you could shed some insight if possible!! 


                            (sorry this is a little long) So it's the same thing we've been talking about, calculating % difference year over year, only difference is that this is my real data (not the superstore example).  So basically I have two sheets, with the exact same 4 dimensions, same filters and one calculated field.  The weird issue I'm having is that although identical (see pics below), the two sheets are producing a different result for unit share change calculation.  For reference the first one (sheet2(2)  sorry prob should have named them better) is correct.


                            SHEET2(2) - overall viz

                            SHEET2(2) - TABLE CALC (FOR UNIT SHARE CHANGE)




                            Now here is my other sheet (sheet2(4)).  Everything looks exactly the same, but the calculation for the % change is different for some reason that I can not figure out.




                            SHEET2(4) - TABLE CALC (FOR UNIT SHARE CHANGE)


                            So everything should be exactly the same, so I'm completely baffled on how Tableau is producing 2 different results.  The really weird thing is when I hover over the unit share change pill, the calc assistance for the each pill is different for each sheet! Despite having the exact same table calculation options! The one that's working says 'totals summarize values from share display, segment for each retailer, year'.  The incorrect one says 'totals summarize values from retailer, year for each share display, segment.  How is it possible that they are calculating two different ways if they have the same calculation options??


                            SHEET2(2) CALC ASSISTANCE MESSAGE



                            SHEET2(4) CALC ASSISTANCE MESSAGE


                            This isn't a one-off incident either, this is what inspired me to create this post.  I keep getting situations where calculations just don't add up, which makes me wonder if something is broken here or what.  Maybe there's something fundamental I'm missing here, and I would love to get some kind of explanation from someone in-the-know!!


                            Thanks guys!!

                            • 11. Re: Best way to calculate change in % of total over time?
                              Don Wise

                              Hi Robert,

                              Just an observation, then I'll move on , but it looks like you're using NESTED CALCULATIONS, so whatever settings you have in the Edit Table Calc for Unit Share Change will also need to be updated in the underlying calculation (nested).


                              If you click on the down arrow just under 'Nested Calculations' for Unit Share Change you should see the related underlying calculation, which you can then select and change to match the Specific Dimensions to Unit Share Change table calculation.


                              That one gets me every time and hopefully that's your answer...


                              If this answers your question please mark it as correct so that others may find it in their search for a solution in the future.  Best, Don  How to Mark an answer as Correct


                              We would love to hear your feedback on your Tableau Community Forums experience.

                              Please take this 5 minute survey.


                                  Tableau Community Forums | Customer Experience Survey

                              • 12. Re: Best way to calculate change in % of total over time?
                                Robert Hyatt

                                Whoa, consider my mind blown!  That worked!  I had no idea that those options were even a thing (nested).  Thank you Don & Kaz for all the help.  I've realized that I still have a lot to learn about tableau calcs!

                                • 13. Re: Best way to calculate change in % of total over time?
                                  Kaz Shakir


                                  Please refer to Don Wise comment - I'm not really familiar with this issue, but it sounds like Don has come across it before.