Use Value from dimension in same row into expression

[Esli_Araujo]

Example data

LOAD * INLINE [
EXECUTER, SUPPLIER, PRODUCT, VALUE
ANA, ANA, A, 1
ANA, JOHN, A, 1
ANA, ANA, A, 1
JOHN, ANA, B, 1
ANA, ANA, B, 1
ANA, ANA, B, 1
JOHN, ANA, B, 1
];

Expected Result

EXECUTER	EXECUTED TOTAL A	SUPPLIED TOTAL B
ANA	3	4
JOHN	0	0

I tryed to use this set analisys but dont achieved the desired result for product 'B' value
sum({$<EXECUTER = P({1<PRODUCT = {'B'}>} SUPPLIER)>} VALUE)
also tried
If(SUPPLIER = Above(EXECUTER,0),Sum({$<PRODUCT = {'B'}>} VALUE))

and did not work as well =(

basically, what I need is that in my chart, on the row where 'ANA' is the executer, return the sum 'A' product values where 'ANA' is executer,
and the sum of 'B' product values where 'ANA' is the supplier

Ps.: Sorry if you found any grammatical error, English it's not my native language 🙂

[lucasjsantos · Accepted solution]

Try This:

EXECUTED TOTAL A:

SUM(Aggr(sum({<PRODUCT = {'A'}>}VALUE), EXECUTER))

SUPPLIED TOTAL B:

SUM(Aggr(sum({<PRODUCT = {'B'}>}VALUE), SUPPLIER))