How do I calculate total training costs per department?

sosunkwo

Hi there, 

 

Suppose I want to calculate total training costs per department. E.g. say I have:

Department                  Training                Cost ($)
Finance                            A                         50
Finance                            B                         60
Marketing                         A                         50 

Marketing                         D                        100

HR                                    &                        50
Finance                             #                        30

Training rolls-up to a training group (e.g A, B are in alphabet group, and & # are in Sign group)

I have my input module where I've loaded all my training costs. 

 

So my question is: How do I calculate total training costs per department?

 

 

Thanks in advance for your replies. 

 

einas.ibrahim

Hi @sosunkwo 

 

I want to make sure I understand your question. Do you want the output to be something like this (based on your example)

Training	Total Training Cost
A	100
B	60
D	100

 

If not the above please provide a mockup of the desired output.
The solution would most probably be achieved by using the SUM function, but it depends on what we need to sum over.

 

I'm confused by this part of your post

HR                                    &                        50
Finance                             #                        30

Training rolls-up to a training group (e.g A, B are in alphabet group, and & # are in Sign group)

 

jasonblinn

Seems like there are several ways to solve this one so here are a couple of ideas:

Seems like your Training Hierarchy looks like this:

Then you have departments and want to assign training to them. In this case, I have trainings rolling up to a department, but you could also use a fixed list from 1 to however many you need, and then assign the department in a dropdown.

 

Now I have a module where I am Assigning Costs to particular trainings (A Systems Module)

 

 

Then choosing my specific trainings by department that I have added to the list. I use a lookup to bring in the price. This will naturally sum up your answer by department.

If you want to take it one step further, we could create a summary view which would allow you to sum across the training to see how much was spent on each training for each department and to see all subtotals across the board as well.

 

Hope this helps!

Jason

 

 

 

sosunkwo

Hi, einas.ibrahim, 

 

Thanks for your quick response!

 

My desired output  will be:

Department	Training Costs	Notes
Finance	140	(i.e. SUM of training A, B, and #)
Marketing	150	(i.e sum of training A and D)
HR	50	(i.e. just Training &)

 

RE: I'm confused by this part of your post

HR                                    &                        50
Finance                             #                        30
Training rolls-up to a training group (e.g A, B are in alphabet group, and & # are in Sign group)

 

What I mean here is that there are two training groups: Alphabet training group which is evident from the lettered training names (A or B or D) and Sign Training Group, evident from the sign-like training names (&, # and so on). 

 

Two departments can have the same training (like Finance and Marketing doing training A) . However I want to see how much I'm spending in training costs per department. Please let me know if this answers your question. 

 

sosunkwo

Hi, @jasonblinn , 

 

Thanks for your response. You are right about what my training hierarchy looks like. 

 

In this case, I have a set amount for each training in each training group (Alphabet group and Sign Group). Now, I just want to see my total training spend for each department. 

 

My desired output should be a grid that looks like this:

 

Department	Training Costs	Notes
Finance	140	(i.e. SUM of training A, B, and #)
Marketing	150	(i.e sum of training A and D)
HR	50	(i.e. just Training &)

 

 

OR like this 

 

Finance	Marketing	HR
140	150	50

 

Hope this helps clarify what I'm trying to achieve.

jasonblinn

Seems that you get this view by just selecting the levels to show on one of the grids from my earlier post.

 

 

 

I think there are several ways to get to this result, so you will need to decide based on what makes the most sense in terms of how this will be updated. 

 

Jason