P&L Ratio

[upaliwije]

I have prepared the following P&L in my Pivot Table

GROUP_DESCRIPTION	ACTUAL	BUDGET	Act Ratio	Bud_Ratio
SALES	100,000	150,000
COST OF SALES	40,000	70,000	40	47
GROSS_PROFIT	60,000	80,000	60	53
STAFF_EXPENSES	10,000	15,000	10	10
ADMIN_EXPENSES	2,000	3,000	2	2
SELLI_EXPENSES	4,000	6,000	4	4
FINAN_EXPENSES	1,000	1,500	1	1
TOTAL_EXPENSES	7,000	10,500	7	7
OTHER INCOME	3,000	4,500	3	3
NET_PROFIT	50,000	65,000	50	43

I have prepared only the 1st three columns and want calculate the two Ratio columns highlighted in yellow.

Formula for calculation all Group Description Rows(except) are divided by Sales Row.In other words I want to show the ratio against Sales.

Pls help me to write the  expression for the same

[tresB]

Try like:

Act_Ratio=Sum({<GROUP_DESCRIPTION={'SALES'}>}ACTUAL)/Sum(ACTUAL)*100

Bud_Ratio=Sum(BUDGET)*100/Sum({<GROUP_DESCRIPTION={'SALES'}>}BUDGET)

[upaliwije]

Thanks for your reply.

But in the results 100 is shown only against Sales Row Other rows remain blank

[tresB]

I guess field reference is not being right(pivot table), could you post a qvw with sample data?

Update: Or try with bit of correction like:

=Sum(ACTUAL)*100/Sum(total {<GROUP_DESCRIPTION={'SALES'}>}ACTUAL)

=Sum(BUDGET)*100/Sum(total {<GROUP_DESCRIPTION={'SALES'}>}BUDGET)

[upaliwije]

I am attaching sample P&L

[Anonymous · Accepted solution]

PFA

Hope this is your req

[upaliwije]

Thanks both of you