Comparing one value in a row to all other rows in the same group

[LisaStew]

First time post, excited to see the response!

Sample Data Available:
Part Number Warehouse Avg Cost
a 000 $1.25
a 200 $1.35
a 300 $1.55
b 000 $5.60
b 300 $5.65
b 400 $5.60

I am trying to compare one Part Numbers' Avg Cost to the Average Cost when Warehouse=000 for the same part number. In other words, I want the output to look like this:

Part Number Warehouse Avg Cost Comparison to 000
a 000 $1.25 $0.00
a 200 $1.35 $0.10
a 300 $1.55 $0.30
b 000 $5.60 $0.00
b 300 $5.65 $0.05
b 400 $5.60 $0.00

It would be *really* nice to get a %-Variance as well. In other words, like this:

Part Number Warehouse Avg Cost Comparison to 000 Variance
a 000 $1.25 $0.00 0%
a 200 $1.35 $0.10 8%
a 300 $1.55 $0.30 24%
b 000 $5.60 $0.00 0%
b 300 $5.65 $0.05 1%
b 400 $5.60 $0.00 0%

I am using CR v11. THANKS

 

[CoSpringsGuy]

if these are detail level records you have shown in your example...

group by part number hide header and footer

comparision formula
{@compare}
sum({avgcost},{partnumber}) - {avgcost}

variance formula
{@variance}
{@compare}/sum({avgcost},{partnumber})

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

dang i really screwed that up....

add another formula
{@W000}
if {Warehouse} = "000" then {avgcost}

in the two formulas above replace
sum({avgcost},{partnumber})
with
sum({@W000},{partnumber})

sorry about that

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[LisaStew]

Colorado Springs Guy!

Wow! I was making that way too hard! Thanks for your prompt response It worked out very well.