3 Replies Latest reply on Aug 11, 2017 5:54 AM by Hamza Alfadel

    Problem: LoD Expression to work out market share for a companies product

    Hamza Alfadel

      Hi guys,

       

      This is my first post on this forum - so nice to meet you all.

       

      I have been struggling on this problem on Tableau for a while and would really appreciate some help.

       

      -------------------------------------------------------------------------------------------------------------------------------------------

      For a given company A that has product_id's 1,2,3....,N

       

      How do I calculate their share of revenue for a given product id against other companies B,C,...., N with the same product id?

       

      My current idea has been to have the company and product_id in the rows field and then SUM(revenue), and SUM(total_revenue) in the columns field.

       

      The SUM(total_revenue) is a calculated measure with this calculation:

       

      {FIXED [Date], [Product_ID]: SUM([Revenue])}

       

      Where the [Date] dimension is a date range filter for the user.

       

      The other filter in the worksheet is a company filter where the user can choose the company to find the revenue aggregated by that company's product_ids.

       

      My calculation can correctly find the total revenue for the product_id when the company filter is set to all, however, when I set the filter to a particular company, it is not the correct total for that product_id against all companies.

       

      I have attached a copy of an excel sheet with dummy values - it may be clearer to visualise there.

       

       

      Thanks in advance!

       

       

      Tableau Version: 10.2

        • 1. Re: Problem: LoD Expression to work out market share for a companies product
          T G

          Hello Hamza,

           

          Refer the below snapshot. If this is it. I have attached the updated your Dummy_Data and sample workbook.

           

           

          After Re-design the data, you just need to pivot the data and rename as required.

           

           

          I hope this could help you.

           

          TG

          1 of 1 people found this helpful
          • 2. Re: Problem: LoD Expression to work out market share for a companies product
            Jim Dehner

            Good morning

            See my solution attached

             

            First I pulled in your excel sheet and pivoted the data around the product ID

            Then I created a field zn(revenue) to zero out the nulls

             

            the dummy data did not have DATE so I just removed that from the fixed calculation (you will put it back in) - I called it Initial Fixed Calcualtion

             

            The key here is to take the calculation to the filter shelf and select All Values and then add it to Context (right click the field and select Add to Context

             

            It sets the calculation prior to filtering

            so your data unfiltered would look like this

             

             

            And filtered like this

             

            The fixed totals remain the same as does the market share against all competitors - which I believe was the goal

             

            Jim

             

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            1 of 1 people found this helpful
            • 3. Re: Problem: LoD Expression to work out market share for a companies product
              Hamza Alfadel

              Hi T.G.,

               

              Thank you for the answer. Your solution is perfect given what I told you about the problem, but, fault of my own, I don't think I've explained it clearly enough.

               

              I am sourcing the data from an Amazon Redshift database via a Custom SQL. The data therefore comes in the form as attached in the 2nd tab of the excel worksheet below. As it is not static data and connecting live to the source, I cannot modify it in excel to make it suitable for a pivot. The only options I can think of are either to create the pivot in SQL (which I do not know how to do/is possible) or find another solution on tableau.

               

              * The data column in the sample I gave you is just for one day. The SQL query is sourcing for the last 12 months.

               

              Thanks again for all your help,

               

              Hamza