Calculation of percentage in pivot

[savithri_n_s]

Hi,

Can you please help in calculating percentage as below

Attached the qvw file and also excel file of calculation

for example

Facilities & Services total PO line item volume under AAPAC : 1000

Facilities & Services PO lines on Catalog  under AAPAC: 200

Catalog count % for Facilities & Services- 20%

	AAPAC	Avanade
Category group	Catalog Count%
Contractors	11.95%	0.54%
Excluded	1.69%	0.00%
Facilities & Services	31.28%	4.44%
Finance Services	0.00%	0.00%
HR	21.48%	0.00%
IT & Telecom	35.29%	4.77%
Legal Services & Risk	0.37%	0.00%
Marketing & Communications	4.78%	0.62%
Prepaid	60.14%	0.00%
Travel Services	22.46%	0.23%
Unclassified	0.00%	0.00%

[sunny_talwar]

Is above chart is the output you are looking to get or you are getting this, but looking to get something else? Not sure I fully understand what you want to get....

[savithri_n_s]

yes sunny .i want the same calculation shown in table to be done in qvw file.we have the chart in qvw file however the calculation has to be modified

[sibin_jacob]

Hi Savithri,

Create a new straight table.

Dimension as (INV)Category Group

then create the expression as below.

sum({<[(REQ) Line Type] = {"Catalog Item"}>}[(REQ) sum(PO Spend)])/

(sum({<[(REQ) Line Type] = {"Catalog Item"}>}[(REQ) sum(PO Spend)])+sum({<[(REQ) Line Type] = {"Non-Catalog Item"}>}[(REQ) sum(PO Spend)]))

Thanks,

Sibin

[sibin_jacob]

Hi Savithri,

If you want to group the data into Region also, then create a pivot table.

Dimension:

(INV)Category Group

(INV)Region

then create the expression as below.

sum({<[(REQ) Line Type] = {"Catalog Item"}>}[(REQ) sum(PO Spend)])/

(sum({<[(REQ) Line Type] = {"Catalog Item"}>}[(REQ) sum(PO Spend)])+sum({<[(REQ) Line Type] = {"Non-Catalog Item"}>}[(REQ) sum(PO Spend)]))

Thanks,

Sibin

[pooja_prabhu_n]

Hi,

Try this without Total

=count({<[(REQ) Line Type] = {"Catalog Item"}>}[(REQ) Line Type])/

if([(INV)Region]='AAPAC',(count({<[(INV)Region]={'AAPAC'}>}[(REQ) Line Type])),

if([(INV)Region]='Avanade',(count({<[(INV)Region]={'Avanade'}>}[(REQ) Line Type])),

if([(INV)Region]='Europe',(count({<[(INV)Region]={'Europe'}>}[(REQ) Line Type])),

if([(INV)Region]='Global Entities',(count({<[(INV)Region]={'Global Entities'}>}[(REQ) Line Type])),

if([(INV)Region]='Latin America',(count({<[(INV)Region]={'Latin America'}>}[(REQ) Line Type])),

if([(INV)Region]='North America',(count({<[(INV)Region]={'North America'}>}[(REQ) Line Type])),

(count({<[(INV)Region]={'Unclassified'}>}[(REQ) Line Type]))))))))

Thanks,

Pooja