12 Replies Latest reply on Dec 7, 2018 4:31 PM by Jennifer VonHagel

    Creating table with different filters on columns; calculations between columns

    Drew H

      Hi experts - have a pretty specific question hoping to get some input on.

       

      I have a data set that has values for 3 different brands and 13 different categories, and their respective sales data (excel data and tableau attached).

      I basically want to recreate the bottom table in Tableau, showing:

      • the % of total for each category among all categories (column 1)
      • the % of total filtered to a specific brand (Bravo in this case, column 2)
      • I also want to have column 3 that shows the difference between the filtered specific brand vs. the total - e.g. for Category Alpha, Bravo's % of total is 5.4 percentage points less than the overall category % of total (30% - 25%)

      Desired output.png

       

      I've loaded the data in tableau, and am wondering two things -

       

      1) Is there any way to get this on one table/sheet? Right now I can get a the values for the first two columns - but since column 2 is a filter to the specific Bravo brand, I cannot figure out how to consolidate the two (at best I can just format them together to imitate that in a dashboard)

       

      2) How do I create column 3, the difference between Columns 2 & 1? Again not sure how that would work given I can't integrate Columns 1 & 2 so far

       

      Using Tableau 10.2. Your help is greatly appreciated!

        • 1. Re: Creating table with different filters on columns; calculations between columns
          Jennifer VonHagel

          Hi Drew,

           

          Are you ok with using a Parameter to select the Brand?

           

          If so, create a String parameter and populate it with values from your Brand field:

           

          Now create the three metrics you wish to show. These are explicitly written Table calculations.

          All % of Total:  SUM([Value]) / TOTAL(SUM([Value]))

          Brand Selected % of Total: SUM(IF [Brand] = [Select Brand:] THEN [Value] ELSE 0 END) / TOTAL(SUM(IF [Brand] = [Select Brand:] THEN [Value] ELSE 0 END))

          Brand Selected v All: [Brand Selected % of Total] - [All % of Total]

           

          The Brand Title won't update dynamically in this case:

           

          Workbook is attached, hope it helps.

           

          Best,

          Jennifer

          1 of 1 people found this helpful
          • 2. Re: Creating table with different filters on columns; calculations between columns
            Drew H

            I feel like you've saved me from Tableau purgatory a dozen times now already, Jennifer.

             

            Thank you, this is absolutely brilliant!

            • 4. Re: Creating table with different filters on columns; calculations between columns
              Drew H

              Small follow-up question Jennifer - I've got some additional nuance to the same dataset that shows results by date. Let's say I want to have some new columns that show the brand's % of total from one month prior, and the difference Month-Over-Month - how would one add that in? So in this example, the data you manipulated was for the month of September, and these new columns would then show the info for one month back aka August.

               

              Image.png

               

              I learned the parameter piece from your sample, but not sure how to add this layer in - basically, those new blue columns would have to be some sort of function I imagine for Current Date - 1, but not sure how to make two parameters talk to each other (or if that's even possible/required?)

               

              Hoping you could help out this newbie once more updated excel and 10.2 tableau (that you built) attached

              • 5. Re: Creating table with different filters on columns; calculations between columns
                Jennifer VonHagel

                Hi Drew,

                 

                Will your data truly only show month, or will it have a proper date that also accounts for the year, so something like the following.  Also, I recommend it have a proper date if you can control the data source - let me know.

                 

                Best,

                Jennifer

                • 6. Re: Creating table with different filters on columns; calculations between columns
                  Drew H

                  It does have a proper date as you noted above Jennifer - I created a separate column that isolates just the month number as I figured it'd be simpler to get a "month -1" calculation (but may be misinformed, per usual )

                  • 7. Re: Creating table with different filters on columns; calculations between columns
                    Jennifer VonHagel

                    Hi Drew,

                     

                    There are a few ways to do this, give the following calcs in the New Calcs folder a try.

                     

                    I updated your spreadsheet to show months as dates.

                    I created a parameter and filled it with your months

                    In the calcs, CM = Current Month, PM = Previous Month.

                    The 3 highlighted calcs will return the Value volume. I put these in the view and then used a Quick Table Calculation to turn these into % of Total.

                    The two calcs with "Share" in the title are written as Table Calcs explicitly.

                    If you add a Quick filter other than Month to the filter shelf, you need to add it to context because these calcs use the LOD FIXED formula.

                     

                     

                    If you add Month into this view, you can see where the volumes fall.  The Selected Brands CM v PM doesn't show here as it doesn't fall into a particular month the way the calculation is written.

                     

                    Best,

                    Jennifer

                     

                    P.S. I had to save the workbook as a .twb to save it as 10.2.  You can connect it to the attached updated Data.xlsx.

                    • 8. Re: Creating table with different filters on columns; calculations between columns
                      Drew H

                      Hi Jennifer - you are my hero, thank you for taking on this task of teaching this poor soul!

                       

                      A few follow-ups - I may have given a poor explanation for the "Brand Prior Mo" column. Here I'm trying to calculate SUM Brand Bravo / SUM All Values for the Month of September for each category (e.g. Bravo's September Alpha values / all Alpha values, and etc.), and likewise for August.

                       

                      So the data calculated in excel would be the following (and pivot attached)

                      Pivot.png

                       

                      I think the calculation in your 3rd/4th columns were 100%? (the 28% on Beta caught my eye, as I knew that was an outlier immediately) I tried altering it from the dataset you provided, but alas the LOD calculations are not my strong point and I just made a mess of things...

                       

                      Likewise, in your dataset I am having difficulty getting your column 3 (% of Total Selected Brand - CM) to appear? it was not present when I opened the file, and every time I load it in and set to Percent of Total, the entire column just disappears! No idea why it's doing this, perhaps a version issue?

                       

                      Thanks again for all of your help so far, you are a LIFESAVER!

                      • 9. Re: Creating table with different filters on columns; calculations between columns
                        Jennifer VonHagel

                        Hi Drew, yes, I didn't quite catch what your calcs were meant to do; it seems clear now.

                         

                        I can write up the calcs tomorrow afternoon unless someone else jumps in to answer, and will show them in the reply in case there is still an issue with the workbook (I couldn't export to 10.2 with the extract because my version uses .hyper rather than .tde).

                         

                        Best,

                        Jennifer

                        • 10. Re: Creating table with different filters on columns; calculations between columns
                          Jennifer VonHagel

                          Hi Drew, here are the steps written out, and hopefully you'll be able to open the attached 10.2 workbook.

                           

                          I created a Select Month parameter, and populated it from your Month field. This parameter will give us a reference point in the calculations for current month and prior month.  We could automatically detect the latest month in your data and use that, but then you wouldn't be able to control what month you wanted to see; it would simply always be the latest month.  With this option, you do need to manually add a new month value to the parameter each month.

                           

                          These two calculations are using FIXED for the purpose of filtering only for the values in the Current Month (month selected in parameter) in All Brands # CM; and filtering for only the values in the Previous Month (1 month before month selected in parameter) for All Brands # (PM):

                          All Brands # (CM): { FIXED [Category], [Brand], [Month] : SUM(IF [Month] = [Select Month] THEN [Value] ELSE 0 END) }

                          All Brands # (PM): { FIXED [Category], [Brand], [Month] : SUM(IF [Month] = DATEADD('month',-1,[Select Month]) THEN [Value] ELSE 0 END) }

                           

                          You can see the outcome of these formulas in this table. If we show the plain Value measure, the sums rightly fall into both August and September.  The FIXED PM and CM measures only have values for the months they are meant to.

                          The formulas above can easily modified to show only the Selected Brand:

                          Select Brand # (CM):

                          { FIXED [Category], [Brand], [Month] :

                              SUM(IF [Month] = [Select Month] AND [Brand] = [Select Brand] THEN [Value] ELSE 0 END)

                          }

                           

                          Select Brand # (PM):

                          { FIXED [Category], [Brand], [Month] :

                              SUM(IF [Month] = DATEADD('month',-1,[Select Month]) AND

                                  [Brand] = [Select Brand] THEN [Value] ELSE 0 END)

                          }

                           

                          Select Brand #: IF [Brand] = [Select Brand] THEN [Value] ELSE 0 END

                          This one shows for any month - isn't limited to Current or Previous

                           

                          Now we can build the Select % of All Brands for current and previous months

                          Select/All Brands % (PM): SUM([Select Brand # (PM)]) / SUM([All Brands # (PM)])

                          Select/All Brands % (CM): SUM([Select Brand # (CM)]) / SUM([All Brands # (CM)])

                           

                          And the difference:

                          Select/All Brands % MoM: [Select/All Brands % (CM)] - [Select/All Brands % (PM)]

                           

                          And finally, to summarize the original explicit Table Calculations which show Share of Category:

                          All Brands % of Cat (CM): SUM([All Brands # (CM)]) / TOTAL(SUM([All Brands # (CM)]))

                          Select % of Cat (CM): SUM([Select Brand # (CM)]) / TOTAL(SUM([Select Brand # (CM)]))

                          Select v All Brand % of Cat: [Select % of Cat (CM)] - [All Brands % of Cat (CM)]

                           

                           

                          Best,

                          Jennifer

                          1 of 1 people found this helpful
                          • 11. Re: Creating table with different filters on columns; calculations between columns
                            Drew H

                            Jennifer, you are a saint. Thank you SO much for the detailed explanation and walkthrough, you have no idea how appreciated this is!!