SUM set analaysis if group contains a certain line

[matthewp]

I have a small data set:

PID	PCODE	PVAL
2	N50	£1,000.00
2	B10	£100.00
2	P40	£500.00
3	B10	£100.00
3	P40	£500.00
4	B10	£100.00
4	P40	£500.00

I want to have a straight table that shows PID and PCODE and PVAL

Except if a PID has a PCODE of N50 in it, then their B10 needs to be reduced by 5% of the N50 like so:

PID	PCODE	PVAL
2	N50	£1,000.00
2	B10	£50.00
2	P40	£500.00
3	B10	£100.00
3	P40	£500.00
4	B10	£100.00
4	P40	£500.00

how can i do this?

[sunny_talwar]

May be this:

=If(Count(TOTAL <PID> {<PCODE = {'N50'}>}PCODE) > 0, Sum({<PCODE = {'B10'}>}PVAL)*0.50 + Sum({<PCODE -= {'B10'}>}PVAL), Sum(PVAL))

[MK_QSL]

or

IF(PID = Only({<PID = P({<PCODE = {'N50'}>}PID)>}PID),SUM({<PCODE = {'B10'}>}PVAL)*(1-'50%')+SUM({<PCODE -= {'B10'}>}PVAL),SUM(PVAL))

[matthewp]

It works, but if i make some selections if reverts back to the original value

[sunny_talwar]

What selections are you making?

[matthewp]

Its also only showing people who have the deduction

[sunny_talwar]

Unless you show what you have, I am not going to understand what you are saying