topic Re: Sum Sales based on Field B values matching Field A dimension across entire dataset in Featured Members

Sum Sales based on Field B values matching Field A dimension across entire dataset

Mpfary — Tue, 18 Nov 2025 07:39:02 GMT

I have the following sample data structure:

Field A Field B Sales
11111 11111 1,739.94
2222 99999 -9,306.80
3333 99999 -491.50
4444 11111 0.00
4444 99999 0.00
4444 11111 6,525.00

My Goal:
Create a straight table where:
Dimension = Field A
Expression = Sum of Sales for all rows where Field B equals the current Field A value

Expected Results:
For Field A = 11111, it should sum Sales from ALL rows where Field B = 11111 (not just where Field A and Field B match on the same row):

Field B = 11111, Sales = 1,739.94

Field B = 11111, Sales = 0.00

Field B = 11111, Sales = 6,525.00

Total = 8,264.94

What's the most efficient way to create this cross-row relationship where for each Field A dimension value, I sum ALL Sales where Field B matches that value anywhere in the dataset?

Re: Sum Sales based on Field B values matching Field A dimension across entire dataset

Nagaraju_KCS — Tue, 18 Nov 2025 07:50:47 GMT

Try this

Sum({<[Field B] = P([Field A])>} Sales)

Re: Sum Sales based on Field B values matching Field A dimension across entire dataset

Mpfary — Tue, 18 Nov 2025 07:55:05 GMT

I have already tried this

Sum({<Field_B = {"=$(=Only(Field_A))"}>} Sales) - Only sums same-row matches
Sum({<Field_B = P(Field_A)>} Sales) - Same issue

Re: Sum Sales based on Field B values matching Field A dimension across entire dataset

Nagaraju_KCS — Tue, 18 Nov 2025 08:18:39 GMT

are you expecting this ?

Re: Sum Sales based on Field B values matching Field A dimension across entire dataset

Mpfary — Tue, 18 Nov 2025 08:41:29 GMT

Yes, that's what I'm expecting

Re: Sum Sales based on Field B values matching Field A dimension across entire dataset

Nagaraju_KCS — Tue, 18 Nov 2025 08:44:32 GMT

Try this

RawData:
Load * inline [
Field A, Field B, Sales
11111, 11111, 1739.94
2222, 99999, -9306.80
3333, 99999, -491.50
4444, 11111, 0.00
4444, 99999, 0.00
4444, 11111, 6525.00
];

NOCONCATENATE
Aggr:
LOAD
[Field B] as [Field A],
'Aggregated' AS [SourceType],
Sum(Sales) AS [Sales]
RESIDENT RawData
Group by [Field B];

RowLevelData:
NOCONCATENATE
LOAD
[Field A],
'Detail' AS [SourceType],
[Sales]
RESIDENT RawData
where [Field A]<>[Field B];

FinalTable:
Concatenate(RowLevelData)
LOAD *
RESIDENT Aggr;

DROP TABLES RawData, Aggr;

Re: Sum Sales based on Field B values matching Field A dimension across entire dataset

Mpfary — Tue, 18 Nov 2025 08:44:46 GMT

However, only for row 1

Re: Sum Sales based on Field B values matching Field A dimension across entire dataset

Nagaraju_KCS — Tue, 18 Nov 2025 09:58:47 GMT

May be this

RawData:
Load * inline [
Field A, Field B, Sales
11111, 11111, 1739.94
2222, 99999, -9306.80
3333, 99999, -491.50
4444, 11111, 0.00
4444, 99999, 0.00
4444, 11111, 6525.00
];

FieldB:
LOAD
[Field B],
Sum(Sales) AS Sales_B
RESIDENT RawData
GROUP BY [Field B];

Left Join ([RawData])
LOAD
[Field B] AS [Field A],
Sales_B
RESIDENT FieldB;

NoConcatenate
Load [Field A], [Field B], IF(LEN(Sales_B)=0, Sales, Sales_B) as Sales
Resident RawData;

Drop table RawData;

DROP TABLE FieldB;

EXIT Script;