Hi there,
I have a special kind of challenge 🙂 I want to get the following pivot table (see the following example):
| Project | Total | Employees | Freelancer |
|---|
| Actual | Plan | Actual | Plan | Actual | Plan |
| A | 11.11% | 8.03% | 11.96% | 10.71% | - | - |
| B | 62.73% | 77.38% | 59.90% | 70.50% | 99.78% | 98.00% |
I already found the formula for the columns "Actual": sum(Cost)/ sum(total <Status> Cost)
My challenge now is to find the right formula for the column "Plan" because I want to achieve a weighted average in the total values. I that I want the value of "Employee" count 3 times and "Freelancer" 1 time.
Example: Project B
The plan for employees (70.5) times 3 (=211.5) + plan of freelancer (98) times 1 = 309.5 divided by 4 equals a weighted total average of 77.38.
For the project B I already got the fitting formula:
((sum({<Status= {"Employee"} >}distinct Plan)*3)
+
(sum({<Status= {'Freelancer'} >}distinct Plan)*1))
/
(if(Status='Employee',3,
if(Status='Freelancer' ,1,4))) // Divisor depends on in which Status the calculation takes place
However, for Project A I cant make it work. As there are empty or null values in the Status "Freelancer", for the total value the divisor will always equal 3 as it appears there are only employees.
So, how can I achieve a division in the total value always by 4? I already tried diverse funktions with isnull but I cant find a solution.
Thank you so much for your help!
