8 Replies Latest reply on Jun 13, 2018 8:38 AM by Jim Dehner

    Creating a share measure vs a fixed line in my data set

    Naila Ahmed

      Hi Everyone,

       

      I am trying to create a share measure as a calculated field in Tableau. I have displayed below a very simple example with the sales for a category and 2 companies (A and B) - the value share for each company is the proportion of sales vs the category.

       

      Value Sales
      Value Share
      Category250100
      Company A10040
      Company B15060

       

      The only way I have been able to do this right now is using the following formula: (SUM([Value Sales]) / TOTAL(SUM([Value Sales])))*100

       

      This formula is only correct however if the sum of company sales equal the category (as shown in my example above), however if there are company sales missing, using the above formula will not give me a true value share - see example below:

       

      Value SalesValue Share using formulaCorrect Value Share
      Category250
      Company A1005040
      Company B1005040

       

      I need the formula to share against the category line and not against the total of all company sales.

       

      I have attached a Tableau workbook with some very simple data - any help with this is greatly appreciated!

       

      For context I need to create a number of share and index measures with a rather complex data set but I am hoping I can leverage any insight I can get on this simplified example as the principles should be the same.

       

      Thank you in advance for any help with this!

       

      Naila