- SAP Managed Tags:
Hi All,
We have a requirement for Expenses planning to happen storewise using the microsoft excel add-in.
Based on the total sales value of the store, the value of each expense GL A/c needs to be divided against the total sales value.
CURRENT
Expected calculation is shown below
(GL Expense/ Sales of that store)
Note - I do know there is a way to write the formula for each GL A/c and divide it by the Income GL group (in the GL Dimension)
But this would be hardcoding the formula for each GL A/c.
Hence, is there a way for the calculation to take the Storewise amount present in the row & divide it by the storewise sales GL grouping, without having the need to hardcode the value for each Expense GL Account ?
@N1kh1l any inputs in terms of achieving this requirement ?
So that whatever GL's the user selects in the filters & those are visible in the rows, system can divide that by the Income GL group for the store?
Expectation is to be bring the sales GL group for that store against all the expenses of that store, so that a % of the expense against the sales of that store can be shown. (below table for reference)
Sales for this store in this month is Rs 500/-. Hence, it is expected that against all the expense GL's, the sales amount is displayed of Rs 500/-
| GL | Store | Amount | Sales | % |
| Electricity | A | 100 | 500 | 20% |
| Rent | A | 150 | 500 | 30% |
| Warehouse | A | 200 | 500 | 40% |
| Sales | A | 500 | 500 | 100% |
Currently, this is happening, by applying LOOKUP or RESTRICT or SUBTOTAL formula's.
System is bringing the sales values against only the sales GL account, but it is not fetching the same sales value for expenses such as electricity.
Hence, we are unable to derive the expense as a % of sales for that respective store.
| GL | Store | Amount | Sales | % |
| Electricity | A | 100 | 0 | #DIV/0! |
| Rent | A | 150 | 0 | #DIV/0! |
| Warehouse | A | 200 | 0 | #DIV/0! |
| Sales | A | 500 | 500 | 100% |
Can you please let us know what way we can achieve this in SAC ?
Regards,
Gavin.
Hi @WahidKausar , is this result your expected one?
The model setting in my test is below.
dimensions and base measures
calculated measures
Sarau
| User | Count |
|---|---|
| 7 | |
| 5 | |
| 4 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.