Issue with Calculated filed in Pivot Table (wrong output calculated)

[martinrt]

I have a pivot table that is pulling together sales information to determine AR outstanding. Each product has a unique product code, a quantity sold, a full price, and a down payment amount. Essentially, I am taking the full price - down payment, and then multiplying by the quantity to determine a receivable. The problem is, the unique product code can appear multiple times in the data set. When I add the calculated field, the out put comes up as a multiple of the correct answer, and the multiple is always the number of times the product code shows up in the source data.
Example:
Product Quantity Price Down Payment AR (calc)
12345 2 100 25 150
12345 4 100 25 300

In Pivot table
12345 6 100 25 900

Any thoughts on how to correct this? I've tried a number of work arounds and nothing is solving the issue.

[queuesef]

your data is 'different'. Most people take the Quantity and multiply it by the Price to get an Extended amount, then deduct the down payment from that to get Outstanding /AR.
your formula just doesnt make sense to me.
1st item: 200 (2 x 100) less 25 = 175 AR
2nd item 400 (4 x 100) less 25 = 375 AR
Slow down...attach a good example, not your makeshift info that doesn't line up.
KISS keep it simple

[martinrt]

Sorry my example came across 'different'. My 'make shift' info actually does make sense...you picked up that the price should be multiplied by the quantity, but somehow thought that same straight forward logic shouldn't be applied to the deposit? That, and the belittling comments aside, although the first example seemed pretty clear to me, here's one that spells it out, half of which you already got.

(2*100) - (2*25) = 150 OR (100-25) * 2
(4*100) - (4*25) = 300 OR (100-25) * 4

When dropped into a pivot table I should get:

(6*100) - (6*25) = 450 (the sum of the 2 results above)

Instead, the calculated field which is (Price - Deposit)*Quantity or as stated above, but combined, would be
(100-25) * 6 = 450, but the pivot result is 900 (correct answer multiplied by 2 for the 2 instances the product appears).

If the quantities were 1, 1, and 4, still 6 in total but 3 instances where the product code shows up, the result is 1,350 (450*3). The result is being multiplied by the number of times the header shows up. I can prove this by adding a field that is count of product header, and would get 2 in my first example, 3 in my second, and dividing my results by them respectfully, and getting to the correct answer.

I've found a solution using a power pivot, which I don't want to use given the end user of this report, and another solution by adding a count field to the original data set (a 1 for each line in the data) that I divide the result by, but that isn't ideal. I just figured there may be an easier, more logical solution.
I did keep it simple, and you're missing the last S, but I won't try to make you feel like dumb like you tried to do to me.

[queuesef]

my attempt is not to belittle....it is to answer the question only. It is difficult to articulate a problem (many times we take a lot of info for granite).....I apologize if I offended. I don't understand the DownPayment, but that is ok.
As for the last S in KISS, i'm not a fan of the word Stupid and I try not to use it.
Nothing works best like an attached file to aid in solution with examples and how the solution would look. Please do not take initial comments personally....it was hard to figure out what you were trying to say.
we are all attempting to learn/solve here. I'm glad that you did resolve your issue.