8 Replies Latest reply on Aug 26, 2013 7:30 AM by Waldemar Holderbein

    How to create a dynamic Share Calculation

    Waldemar Holderbein

      Hi everyone,

       

      happy to join the Tableau Community. I'm facing a little problem. Maybe someone is able to help me

      I have two Quick Filters (Product, Market) and a measure sales.

      What I try to do is, to calculate a total, which depends on the selection in the Market Filter, and calculate based on this total

      a share of sales based on the selection in the product filter.

      Attached is a dummy dashboard.

      Hope someone can help me.

       

      Best regards

      Waldemar

        • 1. Re: How to create a dynamic Share Calculation
          Tom Barnes

          Waldemar,

              You need to use Parameters.  By creating a Market parameter as your filter instead of the dimension Market you will be able to filter sales by Market and by Product in a more effective manner.

           

              I have updated your example workbook with the correct way to use this parameter in your data.  I hope this helps.

           

          Tom

          • 2. Re: How to create a dynamic Share Calculation
            Waldemar Holderbein

            Hi Tom,

             

            thanks for your answer and help. I think I should explain my r equirement a little bit more.

            1) The total market should be calculated based on the selection in "Market"e.g. If the market is "a" the total market should be the sum of sales in market "a". If I now pick a product "b" the SoM should be the sales of this product/total market. In the current settings the total market is always determined through the Product selection.

            2) Ideally, this works for 5 different Markets. Market 5 is a submarket of Market, Market 4 a submarket of 3 etc.

            3) I took Quick filters instead of Parameters becaus of the possibility to show "only relevant values" in the lower markets.

             

             

            Hope this clarifies my problem

             

            Kind regards

            Waldemar

            • 3. Re: Re: How to create a dynamic Share Calculation
              Prashanth Shenoy

              Hi Waldemar,

               

              I have created separate sheets for Total and SoM. Applied only the Market filter on the Total sheet. Included both the sheets in a dashboard.

              Please check if it works for you.

               

              Regards,

              Prashanth

              • 4. Re: How to create a dynamic Share Calculation
                Waldemar Holderbein

                Hi Prashanth,

                 

                thank you for your help

                What's still not working is the product selection.

                As soon as I select e.g. product "a" or "b" the SoM is 1. And this is the problem I'm facing all the time.

                It should be displayed the SoM based on my selected product and the Market I chose.

                 

                Kind regards

                Waldemar

                • 5. Re: How to create a dynamic Share Calculation
                  Tom Barnes

                  Wouldn't you just need to set SoM to compute using "year" instead of "product" or am I not understanding the purpose of SoM?

                  • 6. Re: How to create a dynamic Share Calculation
                    Waldemar Holderbein

                    Hi Tom,

                     

                    I thought so, too. But if I would do so, I had the same total value for every year.

                    I try to explain it different.

                    I have two elements which determine the SoM.

                    1) The Sales of my Product which I choose in the Filter drop down.

                    2) The Sales of the Total Market. It's basically a sum of all the products which belong to a certain market.

                    These two elements are always dependet on the time. That means if I display by calendarweek, the Sales of the product and the total market differs from week to week.

                    My problem is,

                    If I pick a Product, the total market is directly cut down to just this one product instead of my selection in the Market filter/parameter.

                    I can't find a way to tell Tableau "Total Market = based on Market Selection, ignore Product filter"

                    • 7. Re: Re: How to create a dynamic Share Calculation
                      Tom Barnes

                      Ok, I found a workaround but I don't know if you'll like it.  It goes back to using parameters which means you won't be able to use the quick filter show only relevant values but it is the only way I could get this calc to work.

                       

                      Check out the attached workbook. What I did was create a ProductFilter Parameter.  I then created three calculated fields.

                      ProductSales

                      IF [Product] = [ProductFilter] THEN

                      [Sales]

                      END

                       

                      Total Sales

                      SUM([ProductSales])/TOTAL(SUM([Sales]))

                       

                      Products (Dimension)

                      IF [Product] = [ProductFilter] THEN

                      [Product]

                      END

                       

                      So then you throw ProductSales where sales would be and Total Sales where SoM would be and Products where Product would be.  Show the parameter "ProductFilter" and then quick filter your market.  The only other thing you need to do is in a crosstab it is going to show you the "Null" Row.  You will want to hide this row.  Don't exclude the Nulls or it won't work.

                      1 of 1 people found this helpful
                      • 8. Re: How to create a dynamic Share Calculation
                        Waldemar Holderbein

                        Hi Tom,

                         

                        I had hoped that there would be a way without using the Products as a Parameter.

                        In my case, I have a Productlist which contains 10.000 items. A Parameter without the show relevant values is quite a mess

                        Nevertheless, I thank you very much for helping me. I really appreciate your help.

                         

                        Kind regards

                        Waldemar