P & L RATIO

[sagaraperera]

DEAR ALL

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

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 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

SAGARA

[shanemichelon]

Act ratio:

=Sum(ACTUAL) / Sum({$ <DESCRIPTION={"SALES"}>}  Total ACTUAL)

Budget ratio:

=Sum(ACTUAL) / Sum({$ <DESCRIPTION={"SALES"}>}  Total BUDGET)

The set analysis limits the expression to only the SALES records.  The total keyword extends the sum across all the rows of the table (therefore picking up the SALES row.

[sagaraperera]

thanks dear it is done

sagara

[shanemichelon]

If you are happy with my Answer, please mark my response as the correct answer