Total values in pivot table: weighted average

[Anonymous]

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!

[sunny_talwar · Accepted solution]

May be try this

((sum({<Status= {"Employee"} >}distinct Plan)*3)

+

(sum({<Status= {'Freelancer'} >}distinct Plan)*1))

/

(If(Dimensionality() = 0, 4

     If(Status = 'Employee', 3,

     If(Status = 'Freelancer', 1))))

[Anonymous]

Hey thank you so much! That was really the right clue. In fact I had to change it to SecondDimensionality() because I needed the total of the columns not the rows but you put me on the right path!

Have a great weekend!