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>
[TD="align: center"]4[/TD]
[TD="bgcolor: #305496"]Bus Unit[/TD]
[TD="bgcolor: #305496"]Item Code[/TD]
[TD="bgcolor: #305496"]Fee[/TD]
[TD="bgcolor: #305496"]Count of Item Code[/TD]
[TD="bgcolor: #305496"]Sum of AMT[/TD]
[TD="bgcolor: #305496"]Should be[/TD]
[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]BusUnitH[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"] 16,079 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]16941873907[/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"][/TD]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]104[/TD]
[TD="align: right"] $ 73.85 [/TD]
[TD="align: right"] 1 [/TD]
[TD="align: right"] $ 74 [/TD]
[TD="align: right"] $ 73.85 [/TD]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]105[/TD]
[TD="align: right"] $ 37.15 [/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"] $ 149 [/TD]
[TD="align: right"] $ 74.30 [/TD]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]110[/TD]
[TD="align: right"] $ 130.20 [/TD]
[TD="align: right"] 839 [/TD]
[TD="align: right"] $ 91,650,514 [/TD]
[TD="align: right"] $ 109,237.80 [/TD]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]116[/TD]
[TD="align: right"] $ 65.20 [/TD]
[TD="align: right"] 10,969 [/TD]
[TD="align: right"] $ 715,178.80 [/TD]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]132[/TD]
[TD="align: right"] $ 227.70 [/TD]
[TD="align: right"] 83 [/TD]
[TD="align: right"] $ 1,568,625 [/TD]
[TD="align: right"] $ 18,899.10 [/TD]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]133[/TD]
[TD="align: right"] $ 114.00 [/TD]
[TD="align: right"] 53 [/TD]
[TD="align: right"] $ 320,226 [/TD]
[TD="align: right"] $ 6,042.00 [/TD]
[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11700[/TD]
[TD="align: right"] $ 26.60 [/TD]
[TD="align: right"] 84 [/TD]
[TD="align: right"] $ 187,690 [/TD]
[TD="align: right"] $ 2,234.40 [/TD]
[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]13757[/TD]
[TD="align: right"] $ 62.05 [/TD]
[TD="align: right"] 1 [/TD]
[TD="align: right"] $ 62 [/TD]
[TD="align: right"] $ 62.05 [/TD]
[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]13760[/TD]
[TD="align: right"] $ 679.20 [/TD]
[TD="align: right"] 35 [/TD]
[TD="align: right"] $ 832,020 [/TD]
[TD="align: right"] $ 23,772.00 [/TD]
[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"]14221[/TD]
[TD="align: right"] $ 44.65 [/TD]
[TD="align: right"] 3,270 [/TD]
[TD="align: right"] $ 477,437,985 [/TD]
[TD="align: right"] $ 146,005.50 [/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30084[/TD]
[TD="align: right"] $ 50.00 [/TD]
[TD="align: right"] 543 [/TD]
[TD="align: right"] $ 14,742,450 [/TD]
[TD="align: right"] $ 27,150.00 [/TD]
[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30087[/TD]
[TD="align: right"] $ 25.05 [/TD]
[TD="align: right"] 197 [/TD]
[TD="align: right"] $ 972,165 [/TD]
[TD="align: right"] $ 4,934.85 [/TD]
</tbody>
J | K | L | M | N | O | |
---|---|---|---|---|---|---|
$ 7,844,796,257 | ||||||
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="bgcolor: #305496"]Bus Unit[/TD]
[TD="bgcolor: #305496"]Item Code[/TD]
[TD="bgcolor: #305496"]Fee[/TD]
[TD="bgcolor: #305496"]Count of Item Code[/TD]
[TD="bgcolor: #305496"]Sum of AMT[/TD]
[TD="bgcolor: #305496"]Should be[/TD]
[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]BusUnitH[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"] 16,079 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]16941873907[/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"][/TD]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]104[/TD]
[TD="align: right"] $ 73.85 [/TD]
[TD="align: right"] 1 [/TD]
[TD="align: right"] $ 74 [/TD]
[TD="align: right"] $ 73.85 [/TD]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]105[/TD]
[TD="align: right"] $ 37.15 [/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"] $ 149 [/TD]
[TD="align: right"] $ 74.30 [/TD]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]110[/TD]
[TD="align: right"] $ 130.20 [/TD]
[TD="align: right"] 839 [/TD]
[TD="align: right"] $ 91,650,514 [/TD]
[TD="align: right"] $ 109,237.80 [/TD]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]116[/TD]
[TD="align: right"] $ 65.20 [/TD]
[TD="align: right"] 10,969 [/TD]
[TD="align: right"] $ 715,178.80 [/TD]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]132[/TD]
[TD="align: right"] $ 227.70 [/TD]
[TD="align: right"] 83 [/TD]
[TD="align: right"] $ 1,568,625 [/TD]
[TD="align: right"] $ 18,899.10 [/TD]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]133[/TD]
[TD="align: right"] $ 114.00 [/TD]
[TD="align: right"] 53 [/TD]
[TD="align: right"] $ 320,226 [/TD]
[TD="align: right"] $ 6,042.00 [/TD]
[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11700[/TD]
[TD="align: right"] $ 26.60 [/TD]
[TD="align: right"] 84 [/TD]
[TD="align: right"] $ 187,690 [/TD]
[TD="align: right"] $ 2,234.40 [/TD]
[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]13757[/TD]
[TD="align: right"] $ 62.05 [/TD]
[TD="align: right"] 1 [/TD]
[TD="align: right"] $ 62 [/TD]
[TD="align: right"] $ 62.05 [/TD]
[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]13760[/TD]
[TD="align: right"] $ 679.20 [/TD]
[TD="align: right"] 35 [/TD]
[TD="align: right"] $ 832,020 [/TD]
[TD="align: right"] $ 23,772.00 [/TD]
[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"]14221[/TD]
[TD="align: right"] $ 44.65 [/TD]
[TD="align: right"] 3,270 [/TD]
[TD="align: right"] $ 477,437,985 [/TD]
[TD="align: right"] $ 146,005.50 [/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30084[/TD]
[TD="align: right"] $ 50.00 [/TD]
[TD="align: right"] 543 [/TD]
[TD="align: right"] $ 14,742,450 [/TD]
[TD="align: right"] $ 27,150.00 [/TD]
[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30087[/TD]
[TD="align: right"] $ 25.05 [/TD]
[TD="align: right"] 197 [/TD]
[TD="align: right"] $ 972,165 [/TD]
[TD="align: right"] $ 4,934.85 [/TD]
</tbody>
Sheet1