Calculation in Pivot Table

[upaliwije]

Hi Freinds

I have following pivot table

DESCRIPTION	ACTUAL	BUDGET	VARIANCE	ACT_RATIO	BUD_RATIO	DISCOUNTED_@_BUD_RATIO	EXPECTED VALUE IN COLUMN 7
SALES	8,300,839	7,947,000	353,839	100	100	8,300,839	8,300,839
LABOUR	561,789	1,234,564	672,775	7	16	1,289,533	1,289,533
MATERIAL	1,170,307	475,300	(695,007)	14	6	496,463	496,463
COMMISSION	965,125	938,137	(26,988)	12	12	979,907	979,907
GROSS_PROFIT	5,603,619	5,298,999	304,620	68	67	5,534,936	5,534,936
STAFF_EXP	893,494	111,039	(782,455)	11	1	893,494	893,494
ADMIN_EXP	937,451	970,700	33,249	11	12	937,451	937,451
SELLING_EXP	609,639	422,696	(186,943)	7	5	609,639	609,639
TOTAL_EXPENSES	2,440,584	1,504,435	(936,149)	29	19	2,440,584	2,440,584
OTH_INCOME	80,000	48,000	32,000	1	1	50,137	50,137
NET_PROFIT	3,243,035	3,842,564	(599,529)	39	48	4,013,654	3,144,490

The last two columns have identical figures except the net profit row. I want  to calculate the net profit figure (gross profit-Total Expenses+Oth Income). Pls help me achieve this. I have attached my QVW file and supporting Excel Files please.

[upaliwije]

Can Someone pls have look at my query and give solution

[upaliwije]

Will you please help me to solve my issue

[khasimvali85]

Hi Upali,

          Use like this =sum(gross profit-Total Expenses+Oth Income)

Regards,

Khasim.

[Anonymous]

Hi,

use this expression for DISCOUNTED_@_BUD_RATIO, it produces the desired result:

if(DESCRIPTION <> 'NET_PROFIT' ,

If(DIS=3,SUM(ACTUAL),Sum(BUDGET)/$(=$(=vBudget))*$(=$(=vA_Ratio)))

,

SUM( TOTAL  {< DESCRIPTION = { GROSS_PROFIT,OTH_INCOME } >} If(DIS=3,ACTUAL,(BUDGET/$(=$(=vBudget))*$(=$(=vA_Ratio)))))-

SUM( TOTAL  {< DESCRIPTION = {TOTAL_EXPENSES} >}If(DIS=3,ACTUAL,BUDGET/$(=$(=vBudget))*$(=$(=vA_Ratio))) )

)

Best Regards

Stefan

[upaliwije]

Will u please update the expression in QV document and attach the same

[Anonymous · Accepted solution]

Hi,

see the attached QVW.

Best regards

Stefan

[upaliwije]

Thanks a lot

Will U also help to calculate Actual Ratio Relating each year in the Year Sheet

DESCRIPTION	ACTUAL    2014	ACT_RATIO	ACTUAL    2013	ACT_RATIO
SALES	7,332,885	100	967,954	100
LABOUR	339,527	7	222,262	7
MATERIAL	831,338	14	338,969	14
COMMISSION	890,125	12	75,000	12
GROSS_PROFIT	5,271,896	68	331,722	68
STAFF_EXP	853,494	11	40,000	11
ADMIN_EXP	857,451	11	80,000	11
SELLING_EXP	509,639	7	100,000	7
TOTAL_EXPENSES	2,220,584	29	220,000	29
OTH_INCOME	40,000	1	40,000	1
NET_PROFIT	3,091,312	39	151,722	39