i got stuck on a Problem several hours now and cant find a way to solve it. I got a table like this:
|UserId||WorkingDuration||WorkDate||Type A||Type B|
Type A and Type B are two products some workers create Type A's and some create Type B's now i want to calculate the productivity of each Type. The Workers with UserId 1 and 4 work on Type A so the productivity is their Workduration/sum of TypeA.
The Workers with UserId 2 and 3 are working on Type B so their Productivity is their Workduration/sum of Type B.
Now i want to create a KPI for the Productivity of Type A and but when i do SUM(TypeA)/Sum(WorkDuration) i always get the Workingdurations from user 2 and 3 in it as well so it doesnt show the right productivity of the Type A. Any elegant solutions to fix it?