How to calculate Percent of Total in hierarchy and Put on a chart

jakesachs
jakesachs
in Modeling

I'd like to do two things here and would appreciate your help. I think I could do it with 15 steps but there has to be a better way.

I've got a hierarchy called Workstreams with Products, Projects, and Unallocated that accumulates employees hours worked by month. In this module, W2 Workstream detail is the rows and then the line item Total is the Amount (currently an input but in the future may flow in from elsewhere).

What I want is to be able to see what % of Total Hours each thing is, specifically Unallocated but would be nice to see for any child of the list. So if there are 8,100 hours in Jan 25, I want to see what % is Product 1, or Unallocated. I'd like to see this % for the Top Level of W1 All Workstreams most importantly, but also for the W1s so what % each product is for total of products.

Ultimately I'd like to have a chart to display this information in a combination chart where the hours are stacked in columns and then on the other axis it shows the total % allocated, or (1 - % unallocated) by month. Currently not sure that is even possible because what is displayed on the chart below are the list items…..

I don't want to have to create another module that has each of these as Line Items because we will have a process that adds products/projects and it would be tedious updating that module. But, that would be an easy way to calculate the unallocated %.

Image 2-12-25 at 2.26 PM.jpeg

Image 2-12-25 at 2.28 PM.jpeg

Best Answer

  • AjayM
    AjayM
    Answer ✓

    Hi @jakesachs ,

    One thing that I am unsure of the requirement is, whether you need % of total hours at all workstreams level ("% of Level0" lineitem in my ex), or % of total hours at each W1 level ("% of Level1" lineitem in my ex). My solution below gives idea for the both, and the final% lineitem is a mix of both. You may alter the solution based on the requirement.

    For ex: % of Level0 for ANZ = 2000/8100, but % of Level1 for ANZ = 2000/4800. Final % considers both %s and retrieves the % based on the hierarchy level, which you can modify. You may use this final% for chart.

    image.png


    Note: Ideally you should separate the 'hierarchy levels' calculation into SYS module, but I have them all within single module for demo purpose.

    image.png


    image.png
    image.png


    Cheers!

Answers

  • jakesachs
    jakesachs

    Thank you for this! That helps get the answer of % each product or category makes up for the whole in other line items.

    Not to be greedy….but do you have a good way to display this in a chart? So we would want a combination chart with a stacked column that would show everything except the unallocated, so all the Level 2 items except for one of them, and then a line that shows total allocated % over time.

    Instead of showing gross hours, I guess the solution im seeing here is to do a combo chart of that line item % of Level 0 and just put unallocated on its own access.

  • AjayM
    AjayM

    If you meant to show the mix of hours and % against same axis and also separate unallocated item into a different axis, I am not sure if that's achievable.
    But if the ask is simply to separate unallocated item from the rest, and show %, then you might achieve it as below (in my ex, consider Italy and France are equal to unallocated which I switched to line type):

    image.png

Categories