Hi there. I've a table below which shows the cost of production and I want to show that cost per unit for each of the separate items in each plant. Obviously if I try to do this in a normal calculated field in an Excel Pivot I will get a DIV/0 error where there are no units sold.
What my desired outcome is that I can have a column which would show for "Period -1" and "Plant A" the cost of a journey of 5.00 (100/20), wait would be 2.5 (50/20) as would trans. On "Period -2" for "Plant B", the sales would be 1.0 (100/100), wait would be 0.5 (50/100) etc.
I will have various items which will have units against them and I think that PowerPivot can give me the solution to this but I'm unsure as to how. I may also want to remove the "Period" column at some point and in which case I'd want my measure to then sum the Units of the items by Plant.
What my desired outcome is that I can have a column which would show for "Period -1" and "Plant A" the cost of a journey of 5.00 (100/20), wait would be 2.5 (50/20) as would trans. On "Period -2" for "Plant B", the sales would be 1.0 (100/100), wait would be 0.5 (50/100) etc.
I will have various items which will have units against them and I think that PowerPivot can give me the solution to this but I'm unsure as to how. I may also want to remove the "Period" column at some point and in which case I'd want my measure to then sum the Units of the items by Plant.
| Period | Plant | item | Units | Cost |
| Period -1 | Plant A | journey | 20 | 100 |
| Period -1 | Plant A | wait | 0 | 50 |
| Period -1 | Plant A | trans | 0 | 50 |
| Period -1 | Plant B | journey | 50 | 100 |
| Period -1 | Plant B | wait | 0 | 50 |
| Period -1 | Plant B | wait | 0 | 50 |
| Period -2 | Plant B | sales | 100 | 100 |
| Period -2 | Plant B | wait | 0 | 50 |
| Period -2 | Plant B | trans | 0 | 50 |
