How to do this on a pivot table

ali_hijazi — Thu, 18 Oct 2012 14:15:33 GMT

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

Re: How to do this on a pivot table

Thu, 18 Oct 2012 15:18:39 GMT

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