How to make percentages out of a list of data

[Sabrina_Dekkers]

Hi Everybody!

I have some data every now and then that needs to process percentages.

Example: Employees hours written on projects, need to be transferred into a percentage per project of the total time they submitted. So if someone submitted:

15 hours project A
30 hours project B
70 hours project C
2 hours project D
The total is 117 hours, and I want to know how much percent every project is of the total, so Project A is 12,82% project B is 25,64% and C 59,83 percent etc.

Do you gave any idea how I can calculate this the easiest way within a parabola run?

I came up with this (out of the top of my head):

1. divide the report per employee
2. for that employee calculate the total
3. add the total to that employees list
4. use pivot table to be able to calculate with the numbers (calculation is not possible with rows, only columns, right?)
5. calculate the percentage per project
6. use percentage to calculate which part of the total employer costs for this employee (coming from another CSV list) should go to that project

So if employer costs for that month for that employee are 3,000 Project A (12,82%) would make 384.60, Project B (25,64%) 769.20 and C (59,83%) 1,794.90 etc.

That are at least 6 steps per employee, so times 20 that is going to be a a lot of steps on the canvas.
I wonder if I am maybe thinking to complicated, and maybe there is a much faster/easier way to calculate it.

I hope some of the extremely intelligent people here can brainstorm for/with me if there is a better way.

Thank you all in advance!

[sachi]

Hi @Sabrina_Dekkers - I think the Sum by group step will come in really handy here.

Let me know if this kind of solution works:

1. This is what my dummy data in the Google Sheets step looks like:
   
   
2. I first used the Sum by group step to sum the columns “Hours”, grouped by “Employee Name”
3. Then, in the Column Rename step, I’m simply renaming that summed column to “Total Hours”
4. Then, I use a Combine Tables step and connect the Google Sheet first and then the Column Rename step. This helps me get the “Total Hours” column appended to my Google Sheets data.
   
   
5. Lastly, I use the Insert math column step and Format numbers step to get it in the percentage format.
   
   

Since it sounds like you still need to combine total employer costs as a separate CSV, it might make sense to not multiply by 100 quite yet in the Insert Math column step until you’re done calculating the costs.

[Sabrina_Dekkers]

Thank you Sachi, This helps bring down the number of steps to build and still be able to calculate percentages. Just what I was looking for. I was thinking too complicated before, one of my personal pitfalls.

Thank you again for the help, I will be using this a lot!