0

I have table A which describes the number of employees which passed a certain course within a certain branch. The table looks like this:

--------+---------------+---------+--------------------+--------------------+ | Branch | Course_number | Segment | Workers_for_branch | Workers_for_course | +--------+---------------+---------+--------------------+--------------------+ | A | 1 | x | 4 | 1 | | A | 1 | y | 6 | 3 | | A | 2 | x | 4 | 2 | | A | 2 | y | 6 | 1 | | B | 1 | x | 7 | 1 | | B | 1 | y | 5 | 1 | | B | 2 | x | 7 | 2 | | B | 2 | y | 5 | 3 | +--------+---------------+---------+--------------------+--------------------+

Fields interpretation:

  • Branch - A or B
  • Course_number - 1 or 2 (both courses are taught in each branch)
  • Segment - X or Y (both segments are included in each course, hence in each branch).
  • Workers for branch - How many workers within a segment for each branch. i.e. Branch A has a total of 10 workers. 4 of them belong to Segment X and 6 belong to Segment Y.
  • Workers for course - captures the workers that completed the relevant course. i.e, the first row in the table captures 1 employee of branch A, segment x which passed course number 1.

My goal is to represent the ratio of workers which passed the course out of the potential in each branch. My thinking of the following calculation: (workers_for_course) / total workers for branch.

Please note that the denominator must not be filltered according to course_number and Segment, but only by Branch.

i.e. I'd like to represent potential of workers who passed the course in branch A, course 1, segment x:
1/(4+6) = 1/10.

on the contrary, the potential of workers who passed the course in branch A, course 1, segment y: 3/(4+6) = 3/10.

Marged
  • 10,577
  • 10
  • 57
  • 99
Asaf Lahav
  • 57
  • 2
  • 8
  • Hi Alexander, your answer gets me a bit closer to the solution but not quite. I managed to "Pivot" my data to rows according to the explanation in the thread. Not sure what to do next since the "overtreashold " forumla don't seem to fit my case. I guess what I'm looking for is to "lock" (or exclude) the Course_number and Segment fields from affecting my denominator. – Asaf Lahav Nov 15 '15 at 08:54

1 Answers1

1

The general setup is like in this answer: https://stackoverflow.com/a/33715041/5130012 since you try to calculate a value based on just a subset of the whole data set. You can adjust the formula in there to suit your needs by (nearly) just replacing the fieldnames.

Just create a calculated field with this furmula and use it however you like: 

SUM([Workers_for_course]) / {fixed [Branch], [Course_number], [Segment]: sum([Workers_for_branch])}

It should calculate the number you are after.

Community
  • 1
  • 1
Alexander
  • 1,969
  • 19
  • 29