folks, i have a pivot table in which i wish to show the value of sales amount (calculated field) which, for each item, is the product of number of items sold and sale price. i have copied it to an adjacent part of the page to simplify the display for this post. also added the "should be" column. For single items (where the count of item code is 1), the Sum of Amt is correct. For anything more than a single item, the calculation is wrong. Have a look at the third item, 105. there are two counts of this item code. at a value of $37.15, the sum should be $74.30, not $148.60??? Why is that happening?
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
| J | K | L | M | N | O | |
|---|---|---|---|---|---|---|
| 4 | Bus Unit | Item Code | Fee | Count of Item Code | Sum of AMT | Should be |
| 5 | BusUnitH | 16,079 | 16941873907 | |||
| 6 | 0 | 2 | $ - | |||
| 7 | 104 | $ 73.85 | 1 | $ 74 | $ 73.85 | |
| 8 | 105 | $ 37.15 | 2 | $ 149 | $ 74.30 | |
| 9 | 110 | $ 130.20 | 839 | $ 91,650,514 | $ 109,237.80 | |
| 10 | 116 | $ 65.20 | 10,969 | $ 7,844,796,257 | $ 715,178.80 | |
| 11 | 132 | $ 227.70 | 83 | $ 1,568,625 | $ 18,899.10 | |
| 12 | 133 | $ 114.00 | 53 | $ 320,226 | $ 6,042.00 | |
| 13 | 11700 | $ 26.60 | 84 | $ 187,690 | $ 2,234.40 | |
| 14 | 13757 | $ 62.05 | 1 | $ 62 | $ 62.05 | |
| 15 | 13760 | $ 679.20 | 35 | $ 832,020 | $ 23,772.00 | |
| 16 | 14221 | $ 44.65 | 3,270 | $ 477,437,985 | $ 146,005.50 | |
| 17 | 30084 | $ 50.00 | 543 | $ 14,742,450 | $ 27,150.00 | |
| 18 | 30087 | $ 25.05 | 197 | $ 972,165 | $ 4,934.85 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
