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

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

Accepted answers

AjayM

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.

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

Cheers!

All comments

AjayM

Hi @jakesachs ,

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

Cheers!

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

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

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…