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. 

 

Answers

  • 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)

     

  • 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

     

     

     

  • 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. 

     

  • 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.

  • 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