2 Replies Latest reply on Sep 12, 2018 6:42 AM by Brent Janes

    Calculate a percent using count and a total value in a column

    Brent Janes

      Hello,

       

      I'm trying to calculate the out of stock percentage for both entry (OOS on Entry) and exit(OOS on Exit) as well as a resolution percentage for a project I'm working on and need a little help.

      Here are the formulas:

       

      OOS on Entry = Sum of value OOS on entry for a visit / sum of count of expected SKUs 

      OOS on Exit = count of "Yes" in the OOS on Exit Column/ Sum of Expected SKUs 

      Resolution % = (OOS on Entry - OOS on Exit)/OOS on Entry

       

      What is making this difficult is that both OOS on Entry and Count of Expected SKUs are the total for the entire visit and replicate for every SKU.

       

      I have attached a mock workbook for reference

       

      Please let me know if you need any more information

        • 1. Re: Calculate a percent using count and a total value in a column
          Deepak Rai

          Confusing..Can you explain a Bit..

          • 2. Re: Calculate a percent using count and a total value in a column
            Brent Janes

            OOS on Entry

            The values in the columns OOS on entry and Count of Expected SKUs relate to the entire visit (Unique Visit ID) this data is replicated for each SKU in the data set. Thus when I’m trying to calculate %OOS on Entry for the visit Tableau adds the value in the OOS on Entry column as many time as there are SKUs and then divides it by adding Count of Expected SKUs as many times as there are SKUs. This would be fine if I was looking at the average for a single visit but once I start looking at thousands of visits the number is skewed.

             

            Here is an example Visit ID 319350:

                          What I want 3/107 =  2.8%

             

                          What Tableau does (114 *3)/(114*107) = 2.8% but the number of SKUs varies thus impacting the weighting overall

             

             

            OOS on Exit
            Now for this one I need to count all the SKUs where the value in the OOS on Exit column is “Yes” and then divide by the value in the Count of expected SKUs column. I can get the count fine but Tableau adds the value in the Count of Expected SKUs column as many times as there are SKUs.

             

            Here is an example using Visit ID 319350

                          What I want 3/107 = 2.8%

             

                          What Tableau does 3/(114*107) = 3/12,198 = 0.025%

             

            Resolution %

            Has a similar problem to OOS on Entry where the data is replicated which skews the overall average depending on the number of SKUs.

                        

            Here is an example using Visit ID 321938

            What I want (5 – 2)/5 = 60%

             

            What Tableau  does  ((66*5) – 2)/ (66*5) = 328/330 = 99.39%