As you can see on the doc attached, I am trying to calculate an amount for different profiles using their hourly rate.
The formula is in essence:
- if Cat E is personnel (1.* or 2.*) , then vlookup for the corresponding profile (Cat) and apply corresponding rate
- if Cat E is expense (3. and above), use the amount provided (there is no hours here)
If you look at the page 'Step 2 - input' I manage to get the formula working properly:
if([Cat. Expenditure]='1. Personnel (cat. PSS form 1)',sum(MonthlyFigure*[Hourly rate]),
if([Cat. Expenditure]='2. Direct Overhead (cat. PSS form 9)',sum(MonthlyFigure*[Hourly rate]),
But on the sheet 'monthly distribution', when I try to replicate in a KPI box, it does not work.
It seems the aggregation does not happen without me understanding why.
Could you help me understand why there is a different behavior and help me fix this?
Many thanks in advance!