P&L Issue with variable targets

[eldadpaz]

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

[anseglko]

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
)

[eldadpaz]

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.