Stuck with a set analysis formula...

[#1]

Hi,

Could someone please help with some Set Analysis? The goal is to get the results on the "needed column" below. it sums up all deals that have Fields A and B in common. Thanks in advance!

Field A	Field B	Field C	Field D	Needed
Account1	Type1	DealA	10	15
Account2	Type2	DealB	5	12
Account1	Type3	DealC	8	8
Account2	Type2	DealD	7	12
Account1	Type1	DealE	5	15
Account2	Type4	DealF	9	9

[sunny_talwar]

Have you tried creating a chart with Field A and Field B as dimension and Expression:  Sum(Needed)?

[sunny_talwar]

Does this look like what you need?

[#4]

Hi,

"needed" is not a dimension but an expression I'm trying to figure out. Field C and D need to be part of the table, that's the catch...

[stigchel]

You can use an Aggregate function for that,

Aggr(NODISTINCT Sum([Field D]),[Field A],[Field B])

See also attached

[sunny_talwar]

This?

[sunny_talwar]

Oh I see what you want now. Is this looks like what you want?

[#8]

Yes it does!

[buzzy996]

i don't think u will get ur expected output straight way,because when u try to add ur third dimension "Field C" on dimension tab,tht will split/group accordingly.

[sunny_talwar · Accepted solution]

You need Dimension: Field A, Field B, Field C

Expression: =Sum(TOTAL <[Field A], [Field B]>[Field D])