I have a pivot table and it must stay a pivot table that I can not get the total calculations on completed right and because of that the compliance is also wrong. So for this example we are looking at just one location (600) and the month of May for 2017. everything else is right except the ones in red at the bottom.
| Location | Date | SKU Count | Completed | Possible | Compliance |
|---|
| 600 | 5/1/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/2/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/3/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/4/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/5/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/8/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/9/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/10/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/11/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/12/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/15/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/16/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/17/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/18/2017 | 0 | 0 | 1 | 0.00% |
| 600 | 5/19/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/22/2017 | 0 | 0 | 1 | 0.00% |
| 600 | 5/23/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/24/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/25/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/26/2017 | 35 | 1 | 1 | 100% |
| 600 | 5/29/2017 | 0 | 0 | 1 | 0.00% |
| 600 | 5/30/2017 | 0 | 0 | 1 | 0.00% |
| 600 | 5/31/2017 | 35 | 1 | 1 | 100% |
| TOTAL | | 664 | 1 | 23 | 4.35% |
Here is the calculations for each row.
Location is a Dimension
Date is a Dimension
SKU Count = count({<[Type]={'Cycle'}>} DISTINCT [Prd Number])
Completed = if([SKU Count] > 0, 1, 0)
Possible =
Sum(
if (WeekDay([Date]) = 'Mon' and Monday = 'X', 1 ,
if (WeekDay([Date]) = 'Tue' and Tuesday = 'X', 1 ,
if (WeekDay([Date]) = 'Wed' and Wednesday = 'X', 1 ,
if (WeekDay([Date]) = 'Thu' and Thursday = 'X', 1 ,
if (WeekDay([Date]) = 'Fri' and Friday = 'X', 1 , 0 )))))
)
Compliance =
if(Possible = '0' and Completed = '1', '0',
if(Possible = '0' and Completed = '0', '1',
( [Completed] / [Possible])))
Now all the other calculations work and compliance should work if I could get the completed to work correctly. For Completed I have tried wrapping it with SUM() and COUNT() and both did not work.
What can I do to get the completed total to be the correct number (19) rather then 1.
I have to have the chart as a pivot table I can not change it to any other chart.
