14 Replies Latest reply on Oct 8, 2014 6:02 AM by Matt Lutton

    difference in % from total %

    k Busch

      Hello,

       

      I have a table showing share of sales per category (rows) and weekdays (columns) + a total column.

      Shares are calculated by weekday, i.e. the sum of shares per weekday is 100%.

      Formula: SUM([sales]) / TOTAL(SUM([sales]))

       

      Does anybody have an idea, how to calculate the difference in % from category/weekday % to it's total, e.g. 28,2/25,1 = 112 ?

      An example workbook is attached.

       

      This are the shares:

        

      CategorySonntagMontagDienstagMittwochDonnerstagFreitagSamstagTotal
      Bürobedarf28,2%21,6%22,6%27,6%26,7%24,5%25,0%25,1%
      Möbel35,0%37,8%32,9%34,0%33,2%34,9%34,2%34,6%
      Technologie36,7%40,6%44,6%38,4%40,1%40,6%40,8%40,2%
      Gesamtresultat100,0%100,0%100,0%100,0%100,0%100,0%100,0%100,0%

       

      This is, what I want:

        

      CategorySonntagMontagDienstagMittwochDonnerstagFreitagSamstagTotal
      Bürobedarf112%86%90%110%106%97%100%100,0%
      Möbel101%109%95%98%96%101%99%100,0%
      Technologie91%101%111%95%100%101%101%100,0%

       

       

       

      Thanks a lot,

       

      Klaus

        • 1. Re: difference in % from total %
          Matt Lutton

          If you can set up a similar workbook example using Superstore Sales, I'll be happy to take a look.  This scenario could be replicated pretty quickly using that sample data set--but having a workbook to interact with will allow volunteers in this Forum to provide accurate help, as they'll be able to see and interact with some data.

           

          Cheers and best of luck!    

          • 2. Re: difference in % from total %
            k Busch

            Thank your very much.

             

            I just attached an example.

             

             

             

            Klaus

             

             

             

            Von: Matthew Lutton 

            Gesendet: Donnerstag, 2. Oktober 2014 17:40

            An: k Busch

            Betreff: Re:  - difference in % from total %

             

             

             

             

             

             

            <http://community.tableau.com/?et=watches.email.thread> Tableau Support Community

             

             

             

             

             

             

            difference in % from total %

             

             

            reply from Matthew Lutton <http://community.tableau.com/people/matthew.lutton?et=watches.email.thread>  in Forums - View the full discussion <http://community.tableau.com/message/300753?et=watches.email.thread#300753>

            • 3. Re: difference in % from total %
              Matt Lutton

              I was able to get very close to the output you provided by dividing the Percent of Total calc (I edited the table calc you'd set up, so I could give this field a name and re-use it in other calculations) by a WINDOW_AVG of the Percent of Total Calc.  But I'm unclear on your end goal, as I'm not following the Foreign terms in the mock-up you provided (I originally thought the "Samstag" column was your Row Grand Total, but evidently that is your Saturday).

               

              I hope this helps get you closer to what you're looking for.  Someone may be able to modify this to get the exact results you want.  The WINDOW_AVG did not return the same values as the Grand Total, so I know why the results are different, I'm just not sure of the best approach to get what you want and I don't fully understand the problem you're attempting to solve.

               

              This is an example of a Nested Table Calculation--the Percent of Total has a compute using/addressing on "Produktkategorie", and the WINDOW_AVG calc has a compute using on the Order Date.  You can see this inside the "Final % Calc" I have created:

               

              Nested Table Calc help.png

               

              Best of luck! Version 8.2 workbook attached.  I'm happy to try and provide clarity where I can.

              • 4. Re: difference in % from total %
                k Busch

                Thanks, Matthew,

                 

                this is in fact very close, but not exactly what I need unfortunately.

                 

                To get the exact result, we would need something, which returns the percentages in the total column per category (SUM of sales over all days divided by the SUM of ALLsales) instead of WINDOW_AVG([Percent of Total]), which gives only a more or less precise estimation.

                That was exactly the point, were I gave up after several hours.

                 

                 

                PS.: The row grand total is coming after "Samstag", which is "saturday" and is named "total".

                • 5. Re: difference in % from total %
                  Matt Lutton

                  I understand--I was using your mock up for guidance, and it does not include a "Total" column so it was a bit confusing to understand what you wanted at first.  I do understand it now, but it will be challenging to get at the correct value you want.  Tableau computes the grand total by removing any dimensions on the Rows/Columns shelves, but its not always obvious how the math is computed.

                   

                  I'm sure there's a solution, I'm just not able to get at it quickly.  I'll keep my eye on this one, though, as this is the type of stuff that is really challenging to understand in Tableau.

                   

                  If you'd like to learn more about how Tableau computes Grand Totals, I'd recommend reading through this 3-part series by Jonathan Drummey (who I may ping in if I cannot figure this out on my own today)

                  Customizing Grand Totals – Part 1 | Drawing with Numbers

                  • 6. Re: difference in % from total %
                    Matt Lutton

                    OK, with some quick advice from Joe Mako, I was able to come up with the correct values.  This will probably be tricky to understand if you've not used a lot of Nested Table Calcs.

                     

                    I have a meeting in about 5 minutes, but will check back to see what kind of help/further explanation you may need.  Feel free to ask questions.  I created separate Calculated Fields for the "Total" results at different levels.  These calculations all have the same value: TOTAL(SUM(Sales)), but I created three separate calcs so it would be clear what I was doing as I worked through this: one was for calculating the Total with a Compute Using on Product Category, one with the Compute Using on Order Date, and yet another for the Overall Total

                     

                    I hope this helps.  Will come back with more info or to answer questions later.

                     

                    For now, there are some TDT videos available, and there are several that relate to  "Table Calculations"; unfortunately, some of the videos have poor audio/video quality.

                     

                    NOTE: Forgot to attach the TWBX, here it is.


                    The screenshot below is what you'll see in my first worksheet, and is meant to show how I broke down the steps/calcs to return accurate results. 


                    Screenshot.png



                    1 of 1 people found this helpful
                    • 7. Re: difference in % from total %
                      Matt Lutton

                      k Busch:

                       

                      Joe and I are going to discuss this problem this evening (8PM Eastern, 5PM Pacific).  If you want to join, we would welcome you to be present as well.

                       

                      Reach out to both of us via the emails in our profiles, if you're interested.  We'd love to have you on the screenshare!

                      • 8. Re: difference in % from total %
                        Joe Mako

                        Here is what I sent Matt:

                         

                        The key thing for me to parse the request was: 'e.g. 28,2/25,1 '

                         

                        My question then became, where did those two numbers come from?

                         

                        Then I see that each of those numbers is a ratio, and then I figure out what each of those ratios are, hence four SUM(Sales), each at a different level of detail.

                         

                        Step by Step, breaking the problem down into elementary parts, then making those individual parts and putting them together to create the result.

                         

                        So the goal is a ratio of two ratios

                         

                        1st Ratio:

                        SUM(Sales) at Weekday-Category

                        /

                        SUM(Sales) at Weekday

                         

                        2nd Ratio:

                        SUM(Sales) at Category

                        /

                        SUM(Sales) Overall

                         

                        So we need to get the SUM(Sales) at 4 different levels of detail (4 different combinations of Dimensions).

                         

                        The lowest, deepest, most combination of dimensions is the Weekday-Category, so with a Dimension pill for Weekday and Category active on the worksheet, a regular SUM([Sales]) will get us that value.

                         

                        All others need separate table calculation fields, each with their own Compute Using:

                        - For SUM(Sales) at Weekday, we want TOTAL(SUM([Sales])) Partitioned on Weekday and Addressing on Category, so a Compute using of Category

                         

                        - For SUM(Sales) at Category, we want TOTAL(SUM([Sales])) Partitioned on Category and Addressing on Weekday, so a Compute using of Weekday

                         

                        - For SUM(Sales) Overall, we want TOTAL(SUM([Sales])) with no Partitioning and Addressing on both Weekday and Category, so an Advanced Compute using Addressing on both Dimensions

                         

                        We use the TOTAL() function here because we want an aggregation at  different levels. We would use a WINDOW_* function if what we needed was an aggregation of an aggregation.

                        1 of 1 people found this helpful
                        • 9. Re: difference in % from total %
                          Joe Mako

                          Here is a solution detailed in a video at:

                          https://vimeo.com/107876148

                           

                          and the workbook is attached, please let us know if you have any questions Thank you!

                          • 10. Re: difference in % from total %
                            k Busch

                            Hi Joe,

                             

                             

                             

                            I just went through your video. This is 100% great!!! Thank you very much.

                             

                             

                             

                            Yesterday I was a second before giving up.

                             

                            I posted my question (with another text) already about 4 weeks ago in the forum: zero replies. (I will  put a link to your video in it.)

                             

                            I send it to the support: no answer.

                             

                            I spent hours playing around without getting it.

                             

                             

                             

                            So thanks again.

                             

                             

                             

                            Additionaly your video is extremely helpful because it explains very well the difference between partitioning/ addressing and because it describes in an understandable way the interesting concept of nested calculations. When I analyzed my problem I realized I would need something to combine horizontal and vertical addressing in the right order, but I didn’t know how. Now I do.

                             

                             

                             

                             

                             

                            Kindest regards,

                             

                             

                             

                            Klaus

                             

                             

                             

                             

                             

                             

                             

                             

                             

                            Von: Joe Mako 

                            Gesendet: Freitag, 3. Oktober 2014 02:28

                            An: k Busch

                            Betreff: Re:  - difference in % from total %

                             

                             

                             

                             

                             

                             

                            <http://community.tableau.com/?et=watches.email.thread> Tableau Support Community

                             

                             

                             

                             

                             

                             

                            difference in % from total %

                             

                             

                            reply from Joe Mako <http://community.tableau.com/people/joemako?et=watches.email.thread>  in Forums - View the full discussion <http://community.tableau.com/message/300929?et=watches.email.thread#300929>

                            • 11. Re: difference in % from total %
                              k Busch

                              Hello Matthew,

                               

                               

                               

                              thank you very much for all your efforts. You and Joe helped me very much.

                               

                              As a newbie to tableau I can’t say how valuable your support is for me.

                               

                              I am hoping being able to give back something to the community soon.

                               

                               

                               

                              I have a lot of experience working with SPSS and SQL, but this seems to be sometimes obstructive, because there are basic differences in the approach.

                               

                              People coming from the OLAP-world will have less problems, I think.

                               

                               

                               

                               

                               

                              Thanks again,

                               

                               

                               

                              Klaus

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                              Von: Matthew Lutton 

                              Gesendet: Donnerstag, 2. Oktober 2014 20:02

                              An: k Busch

                              Betreff: Re:  - difference in % from total %

                               

                               

                               

                               

                               

                               

                              <http://community.tableau.com/?et=watches.email.thread> Tableau Support Community

                               

                               

                               

                               

                               

                               

                              difference in % from total %

                               

                               

                              reply from Matthew Lutton <http://community.tableau.com/people/matthew.lutton?et=watches.email.thread>  in Forums - View the full discussion <http://community.tableau.com/message/300807?et=watches.email.thread#300807>

                              • 12. Re: difference in % from total %
                                Matt Lutton

                                No problem. I come from a teaching background and also struggled with getting to your final answer. I could have reached out to a number of tableau users but Joe and I have been doing screen shares every week and I am very grateful for his willingness to walk through problems with me.

                                 

                                I hope you can give back some day as well! Glad this was helpful for you.

                                 

                                Cheers!

                                • 13. Re: difference in % from total %
                                  Keith Helfrich

                                  Hi everyone,

                                   

                                  I've written a summary of Joe & Matthew's video to color in the key points of building nested table calcs:

                                   

                                   

                                  Thanks!

                                  Keith Helfrich | LinkedIn

                                  Keith Helfrich | Twitter

                                  • 14. Re: difference in % from total %
                                    Matt Lutton

                                    Excellent.

                                     

                                    There are so many resources out there since the Conference.  I, personally, think it is all good, although I know it means there is more for newcomers to dig through to get to answers they seek -- overall, I'm for more information rather than less.