In Table other Company Data is not getting.

[sachin1]

I have the fields 'Company,' 'Region,' and 'Sales,' and I want the output which field is in bold.

Company	Region	Sales	Company "A" Sales	Sales - Company "A" Sales
A	North	10	10	0
A	South	5	5	0
A	West	6	6	0
A	East	3	3	0
B	North	7	10	-3
B	South	9	5	4
B	West	2	6	-4
B	East	5	3	2
C	North	7	10	-3
C	South	4	5	-1
C	West	9	6	3
C	East	5	3	2
D	North	8	10	-2
D	South	4	5	-1
D	West	9	6	3
D	East	18	3	15

[Kushal_Chawda · Accepted solution]

@sachin1  try below

Data:
LOAD Company,
           Region,
           Sales
FROM table;

Left Join(Data)
Load 
         Region,
         sum(Sales) as [Company A Sales]
Resident Data
where Company='A'
Group by Region;

Final:
NoConcatenate
Load *,
         Sales- [Company A Sales] as [Sales - Company A Sales]
Resident Data;

Drop Table Data;

[madelonjansen · Accepted solution]

I think you could do it with Set Analysis:

Company "A" Sales: Sum({<Company={A}>} TOTAL <Region> Sales)