Conditional Sum Question

[BrunPierre]

ID	R_Type	R_Company	%_CONTRIBUTION
FAR	C	F0	0
FAR	C	F0	40
FAR	F	F5	0
FAR	F	F5	54.17
FAR	T	FS	0
FAR	T	SS	22

Hello guys!

With the above I'd like to perform a conditional s

For every R_Type , sum the %contribution based on the R_company, that is

IF R_Type  = C and R_Company is unique (F0), then 0 + 40 = 40

IF R_Type  = F and R_Company is unique (FS and SS), then 0 + 54.17 = 54.17

IF R_Type  = T and R_Company is not unique (F5 & SS), then 0 + 22 *(2) = 44 because there are two distinct R_Company.

Thanks

[rubenmarin]

Hi, you dont' say what happens when R_Type=C or F and company is not unique, or R_type=T and company is unique.

As a guess, can you try?:

Sum(TOTAL <ID, R_Type> %_CONTRIBUTION)*Count(DISTINCT TOTAL <ID, R_Type> R_Company)

[BrunPierre]

Thanks for the quick response.

R_Type is a single value field i.e It  does not contain F or C. But however when R_Company contains unique R_Company with different % contributions like below ;

FO  1.05

FO 1.5

the resultant should be F0 = 1.05 + 1.5 = 2.55

Could you kindly add this to your earlier expression, thanks.

[rubenmarin]

Hi, what doesn't works using the expression I posted before?

Can you post a sample or expand your example?

[BrunPierre]

Hi,

Attached is the sample as per requested. Hope it's self-explanatory.

Again, thank you.

[rubenmarin · Accepted solution]

Hi,

So, when ID=DMP it should be the same as default?

If(ID='FAR'
,Sum(TOTAL <ID, R_Type> %_CONTRIBUTION)*Count(DISTINCT TOTAL <ID, R_Type> R_Company)
,Sum(%_CONTRIBUTION)
)