2 Replies Latest reply on Apr 16, 2018 4:13 PM by Isis Ibarra

    Percent of Total and Total Calculations

    Isis Ibarra

      Hello Tableau Community!

       

      I've been working on a dashboard that will track down the weekly market share of some companies.

       

      I have access to the the following information:

      • Company Name ID: Due to confidentiality issues, I've just summited the company's id, rather than the actual name.
      • Consecutive Order ID
      • Date: The date when the order was placed.

       

      I need to know the amount of orders placed during the week (# Orders) and the % of the market share. As for now, I've calculated the # Orders with the following formula: ZN(AVG([Consecutive Order Id])) - LOOKUP(ZN(AVG([Consecutive Order Id])), 1), but I'm missing the % of the market share and the total from # Orders . I've tried the following for the % of the market share:

      1. SUM([# Orders])/TOTAL(SUM([# Orders])) --> Argument to SUM (an aggregate function) is already an aggregation, and cannot be further aggregated.
      2. [# Orders]/TOTAL([# Orders]) --> The input to TOTAL() cannot include a Table Calculation function.
      3. And I also tried to do a table calculation using % of Total and/or Running_Sum without success.
      4. And finally, for the totals I tried the Analysis > Totals > Show Row Grand Totals but it isn't giving me the expected sum per row.

       

      Any ideas on how to get this numbers?

       

      I've attached the workbook so it will be easier for you to follow what I'm explaining.

       

      Sample Expected Results

       

      Date13
      4
      6
      Total
      April 16th, 2018     # Orders11,17332987529112,668
      April 16th, 2018    % Market Share88.2%2.6%6.9%2.3%100%
      April 6th, 2018     # Orders13,57239198735915,309
      April 6th, 2018     % Market Share88.6%2.6%6.4%2.3%100%

       

       

      Thanks in advance,

      Isis Ibarra

        • 1. Re: Percent of Total and Total Calculations
          Jeevan Krishna

          Hello,

           

          I do not have right version to open the dashboard attached. I will answer from what I understood from your question.

           

          I guess your calculation of # Orders is complicating your overall situation.

           

          Considering your data is like this

           

           

          You can create count distinct as #Orders instead of the function you are using.

           

           

           

          To get market share , now use any of the options you mentioned in the question, you will be able to create a field which gives you market share. Only thing, you need to make sure your table calculation is table(across) since company IDs are placed across the table.

           

          Hope this helps

          • 2. Re: Percent of Total and Total Calculations
            Isis Ibarra

            Jeevan, thanks for taking the time to answer my question.

             

            You got it almost right! The only issue is that I can't do a COUNTD([Order ID]) since I have only one consecutive order id per week, like the image below:

            Captura de pantalla 2018-04-16 a la(s) 18.00.39.png

             

            So, I need to first do the calculation to obtain the number of orders placed in a week (# Orders) and then obtain the % Market Share :/

             

            Any ideas?

             

            Thanks,

            Isis