Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
thanks dear it is done
sagara
If you are happy with my Answer, please mark my response as the correct answer