Hello!
I'm trying to find a way to calculate a KPI. The KPI measures work efficiency (WE) based on produced hours (PH) divided by attendance hours (AT), simply put.
The plant is divided into three areas. Area 1 is the producing one. Area 2 and 3 are not producing. All the areas are divided into departments. The areas and each department are to have a correctly measured WE as follows.
Departments in area 1 are to be measured by their own PH and AT. Area 1 is to be measured by the sum of PH and the sum of AH.
Departments in area 1 and 2 are to be measured by the PH from area 1 divided by the AT from the department in question.
The areas 2 and 3 are to be measured by PH divided by the cumulative sum of the area itself and the one "below it", i.e. WE(area2)= PH(area1)/(AT(area1)+AT(area2)). I want to point out that this does not apply to the departments in these categories.
---------The challange----------
I've come so far as to have a cumulative AT for ONLY the areas and PH be the same for all areas. However, when it comes to having departments in area 2 and 3 have the PH from area 1 AND to have the departments in area 1 measure WE=PH(department x in area 1)/AT(department x in area 1).
I had a picture that showed the report in its current state but apparently I can't share it due to my company blocking all image sharing sites. I will see if I can fix this when I come home.
And this is by using the following Calculated Fields formulas:
Cum Produced:=CALCULATE(SUM(RouteTransactions[ProcessHours]),FILTER(ALL(dCostCenter[WECat]), dCostCenter[WECat]=1))
WE%:=DIVIDE([Cum Produced],[Cum Attendance])
I do have some solutions in mind, for example using NOT(...) and list all the department names, but that is very rigid and creates a lot of code.
P.S. Filter context is hard!....but interesting
I'm trying to find a way to calculate a KPI. The KPI measures work efficiency (WE) based on produced hours (PH) divided by attendance hours (AT), simply put.
The plant is divided into three areas. Area 1 is the producing one. Area 2 and 3 are not producing. All the areas are divided into departments. The areas and each department are to have a correctly measured WE as follows.
Departments in area 1 are to be measured by their own PH and AT. Area 1 is to be measured by the sum of PH and the sum of AH.
Departments in area 1 and 2 are to be measured by the PH from area 1 divided by the AT from the department in question.
The areas 2 and 3 are to be measured by PH divided by the cumulative sum of the area itself and the one "below it", i.e. WE(area2)= PH(area1)/(AT(area1)+AT(area2)). I want to point out that this does not apply to the departments in these categories.
---------The challange----------
I've come so far as to have a cumulative AT for ONLY the areas and PH be the same for all areas. However, when it comes to having departments in area 2 and 3 have the PH from area 1 AND to have the departments in area 1 measure WE=PH(department x in area 1)/AT(department x in area 1).
I had a picture that showed the report in its current state but apparently I can't share it due to my company blocking all image sharing sites. I will see if I can fix this when I come home.
And this is by using the following Calculated Fields formulas:
Cum Produced:=CALCULATE(SUM(RouteTransactions[ProcessHours]),FILTER(ALL(dCostCenter[WECat]), dCostCenter[WECat]=1))
WE%:=DIVIDE([Cum Produced],[Cum Attendance])
I do have some solutions in mind, for example using NOT(...) and list all the department names, but that is very rigid and creates a lot of code.
P.S. Filter context is hard!....but interesting
