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

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

Confusing..Can you explain a Bit..

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

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%