Dear Forum,
I've got data structured in the following manner:
<tbody>
</tbody>
Based on the cost to date, I'm trying to allocate the cost for the remainder of the month. Imagine that the data set is MUCH larger than this one, and I currently have all the data linked to pivot tables. In the pivot table I'm % of column total in order to do my allocation. I've made a calculated field, using MAX(0,Est.cost-Real.cost) in order to not allocate any more costs to products which have already spent/overspent their allowance.
Now the issue I keep running into is, that the grand total is also a calculation rather than a summation of the information in the calculated field column. So in this example, the sum column% will looks like this:
<tbody>
</tbody>
Now my issue is that I cannot use this allocation for anything as it does not add up to 100. Is there another formula I can use for the calculated field, which will allow me to leave overspent at 0%, and allocate the remaining product's cost% to sum up to 100% in the grand total?
Hope the explanation makes sense, otherwise I'll be happy to elaborate.
I've got data structured in the following manner:
| Product | Estimated cost month | Real Cost to date |
| A | 50 | 60 |
| B | 50 | 20 |
| C | 50 | 30 |
<tbody>
</tbody>
Based on the cost to date, I'm trying to allocate the cost for the remainder of the month. Imagine that the data set is MUCH larger than this one, and I currently have all the data linked to pivot tables. In the pivot table I'm % of column total in order to do my allocation. I've made a calculated field, using MAX(0,Est.cost-Real.cost) in order to not allocate any more costs to products which have already spent/overspent their allowance.
Now the issue I keep running into is, that the grand total is also a calculation rather than a summation of the information in the calculated field column. So in this example, the sum column% will looks like this:
| Product | % allocation of remaining cost |
| A | 0% (since they have overspent) |
| B | 75% (since it's 30 of the 40 remaining total cost) |
| C | 50% (since it's 20 of the 40 remaining total cost) |
| Grand total | 100% (since it's 40 of the 40 remaining total cost) |
<tbody>
</tbody>
Now my issue is that I cannot use this allocation for anything as it does not add up to 100. Is there another formula I can use for the calculated field, which will allow me to leave overspent at 0%, and allocate the remaining product's cost% to sum up to 100% in the grand total?
Hope the explanation makes sense, otherwise I'll be happy to elaborate.
Last edited:
