How to do this on a pivot table

[ali_hijazi]

I got a pivot table that shows the purchases by shop as follows with the following expression

Sum{<[Transaction Type]={'Sales','Purchases'} >}[Total Amount USD])

Shop, Purchases

Bershka East, 4500

Bershka North, 6000

Bershka South, 7500

Boggi East, 400

Boggi North, 300

the above shops correspond to two brands (Bershka and Boggi)

what I want on the pivot table is to see the total by Brand as follows:

Shop,             Purchases, Total by Brand

Bershka East,  4500,        18,000

Bershka North, 6000,        18000

Bershka South, 7500,        18000

Boggi East,       400,         700

Boggi North,      300,         700

Please advise

knowing that I used the following expression for the total by brand

Sum((aggr(sum(total<Brand>{<[Transaction Type]={'Sales','Purchases'} >}[Total Amount USD]),Brand)))

but I got the following:

Shop,             Purchases, Total by Brand

Bershka East,  4500,        18,000

Bershka North, 6000,        0

Bershka South, 7500,       0

Boggi East,       400,         700

Boggi North,      300,        0

[#2]

Hi,

try this in load script:

tab1:

LOAD Shop,

Purchases,

left(Shop,index(Shop,' ')-1) as Brand,

FROM table;

tab2:

load Brand,

sum(Purchases) as Purchases_per_Brand

Resident tab1 Group by Brand;

Now simply use sum(Purchases_per_Brand) as ur 2nd expr in chart.

Hope this helps

Regards