Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I am trying to build a P&L which has several calculated targets, based on actual income (e.g. FoodCost's target should be 42% of actual income).
I've managed to get all targets value to appear correctly for ExpenseType=(Income,Fixed), however, I can't manage to get the variable expenses to show correctly.
For the Target calculation I tried this:
If (
TargetType='Absoloute',
Sum(TargetValue),
Sum(TargetValue)*Sum({<Expense={'Income'}>}ActualValue)*-1
)
This statement results in showing 0.00 for the target value.
Attached are the excel
Appreciate your help
Could it be because the first row is BOTH TargetType='Absoloute' and Expense={'Income'}, so the if-statement skips that when you try to calculate the else value "Sum(TargetValue)*Sum({<Expense={'Income'}>}ActualValue)*-1"?
What happens if you try
If (
TargetType='Absoloute' and Expense<>'Income',
Sum(TargetValue),
Sum(TargetValue)*Sum({<Expense={'Income'}>}ActualValue)*-1
)
Thanks, Anseglko, but it didn't work.
My relative target for Expense=FoodCost is 42% of actual income for each month.
So, if the actual income in March is 65,340$, the FoodCost target for that month should be: 27,442$. This calculation should be done for each month which has actual income.
Please notice that:
1. The relative targets are only for Variable Expenses and not for Fixed Expenses
2. For target variable expenses ONLY, I am trying to multiply the TargetValue of that expense (e.g. 0.42) by the ActualValue (e.g 65,340$) of a different expense (i.e. Income)
I can't figure out why the multiplication is not done and results in 0 value.