How do I calculate total training costs per department?

Highlighted
Contributor

How do I calculate total training costs per department?

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. 

 

5 REPLIES 5
Highlighted
Valued Contributor

Re: How do I calculate total training costs per department?

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)

TrainingTotal Training Cost
A100
B60
D100

 

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)

 

Einas
"Give a Man a Fish, and You Feed Him for a Day. Teach a Man To Fish, and You Feed Him for a Lifetime"
Highlighted
Contributor

Re: How do I calculate total training costs per department?

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. 

 

Highlighted
Certified Master Anaplanner

Re: How do I calculate total training costs per department?

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:

jasonblinn_0-1592423263231.png

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.

jasonblinn_1-1592423383568.png

 

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

jasonblinn_2-1592423435896.png

 

 

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.

jasonblinn_3-1592423482873.png

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.

jasonblinn_4-1592423581523.png

 

Hope this helps!

Jason

 

 

 

Highlighted
Contributor

Re: How do I calculate total training costs per department?

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 AB, 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.

Highlighted
Certified Master Anaplanner

Re: How do I calculate total training costs per department?

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

 

Anaplan_-_Jason_Sandbox_-_DEV.jpg

Anaplan_-_Jason_Sandbox_-_DEV.jpg

Anaplan_-_Jason_Sandbox_-_DEV.jpg

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