5 Replies Latest reply on Mar 20, 2012 11:44 AM by Jonathan Drummey

    Static % of Total (Market Share) calc

    Ryan Shirley

      I'm sure it's an easy fix, but I am trying to make a % of Total (market share) calculation. I need a true, static, % of total calculation, so I need it to NOT be affected by filtering. For example, if I have 10 products, and I want to know what % of sales each product had, then I could put it into a crosstab, and do a table calc based on % of Total. However, if I only want to see 4 of the 10 products (ie filter out the other 6 prodcuts) then it changes the calculation. I have been getting around it by just HIDING the other 6 products, but that seems like such a hassle because then I can't quickly add another product to the mix if I wanted to view more data.

        • 1. Re: Static % of Total (Market Share) calc
          Jonathan Drummey

          In that case, you probably need to do something like what I did in this post:




          You have table calcs to return both the individual data and the aggregate rows, and then apply a filter based on a table calc, so Tableau will do all the computations on the data and then apply the table calc filter.

          • 2. Re: Static % of Total (Market Share) calc
            Ryan Shirley

            I have been working with this trying to get it to do what I want, but I can't seem to figure it out...problem being that it isn't truly static. In your workbook, when I use the Product Type as a Quick Filter, it changes the calculations, which is what I don't want to happen. I almost got around it by using part of your workbook (I made the "Choose Market" calculation) and making that a quick filter. When I made that a quick filter, and deselected certain products, the % of total stayed the same, and the months percentages were also taken away, but the Total Yearly % stayed in...so I had a column header with the Product name, the body of column was blank, and then a total at the bottom of the column. (see attached picture). If I removed the subtotal, it worked properly, but what if they want to see the total year end change (which they do)?


            Image 1.png


            There was this thread too, which seems to have asked for the same thing I'm looking for, and the answer was to just hide the data you dont want to see: http://community.tableau.com/message/160841


            I'm almost thinking hiding it may be the best way around this.

            • 3. Re: Static % of Total (Market Share) calc
              Jonathan Drummey

              What you're running into by adding Product Type to as a Quick Filter to the view is Tableau being "smart" by recognizing that Product Type is the same field in the primary and secondary datasources, and automatically adding that as a relationship. leading to your secondary data then using the Quick Filter as well. Besides the numbers not looking right, you'd be seeing that as the orange/red link icon next to Product Type in the secondary datasource. Since you don't want that to be filtered, you need to go to Data->Edit Relationships... and delete the relationship for Product Type. Then your totals should work again.


              I'm not clear on your workaround, I think trying the above is your best bet since that's the original issue.


              And just to be clear, you'll need to edit the relationships appropriately for any Quick Filters or other dimensions that you'd add to the view on the Rows, Columns, Color, Shape, Size, and/or Level of Detail shelves.



              • 4. Re: Static % of Total (Market Share) calc
                Ryan Shirley

                I think I may have confused you in my last reply...I was trying to say that I took part of your workbook (the "Choose Market" calculation) and implemented it into MY workbook. So, in this case, I do not have a secondary data source in my workbook, and thus can not edit relationships. By putting the Choose Market (I named mine Choose Product) in the filters and using the quick filter, I got the calculations to work correctly (as stated above)...the only problem being that the year end totals would stay in for de-selected products when the Subtotals were enabled on my Year Pill (that was the picture I posted). If I turned off the subtotals, it would display as I wanted.

                • 5. Re: Static % of Total (Market Share) calc
                  Jonathan Drummey

                  Ok, I wasn't clear on two points. One is that you are only using one datasource, the other is that you are trying to use the Total/Subtotal functions in a crosstab. The original post I'd linked to was about working with showing totals in charts, the Total/Subtotal functions are a built-in feature in Tableau and their operation is very specific and has limited customizability. You can do some funky table calculations to override what Totals show, and maybe Subtotals too, see this post for details: http://community.tableau.com/thread/116854, but as I noted in that post the calculations are sensitive to what dimensions are in use.


                  A third point I'm not l clear on is that from the screenshot, I'm not clear on what data is being displayed on the crosstab and, especially important when it comes to table calcs, what the relationships are within your data. If you post a packaged workbook with some sample data, someone here could probably help more.