Hi all,
I have a data set in which I am trying to calc the % of total per week, and can't for the life of me figure it out. I need the % of a field that coincides to the week total for the week it belongs to. Below is an example of the data I have:
Week | Day | Ex1 | Ex2 | Ex3 |
1 | Mon | 10 | 20 | 30 |
1 | Tue | 10 | 20 | 30 |
1 | Wed | 10 | 20 | 30 |
1 | Thu | 10 | 20 | 30 |
1 | Fri | 10 | 20 | 30 |
1 | Sat | 10 | 20 | 30 |
1 | Sun | 10 | 20 | 30 |
2 | Mon | 10 | 20 | 30 |
2 | Tue | 10 | 20 | 30 |
2 | Wed | 10 | 20 | 30 |
2 | Thu | 10 | 20 | 30 |
2 | Fri | 10 | 20 | 30 |
2 | Sat | 10 | 20 | 30 |
2 | Sun | 10 | 20 | 30 |
I am trying to get an output like this:
Week | Day | Ex1 | Ex2 | Ex3 |
1 | Mon | 2% | 5% | 7% |
1 | Tue | 2% | 5% | 7% |
1 | Wed | 2% | 5% | 7% |
1 | Thu | 2% | 5% | 7% |
1 | Fri | 2% | 5% | 7% |
1 | Sat | 2% | 5% | 7% |
1 | Sun | 2% | 5% | 7% |
2 | Mon | 2% | 5% | 7% |
2 | Tue | 2% | 5% | 7% |
2 | Wed | 2% | 5% | 7% |
2 | Thu | 2% | 5% | 7% |
2 | Fri | 2% | 5% | 7% |
2 | Sat | 2% | 5% | 7% |
2 | Sun | 2% | 5% | 7% |
Anyone have any suggestions?
Thanks!
Solved! Go to Solution.
The first step is to summarize the data. Group by Week and Sum the field. Then join the data back to the original data on the week field. Then you use a formula tool to divide the value into the sum.
Hello @tr3nd ,
Hope this helps
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regards
Hi @tr3nd ,
first step is to calculate the "sum by week" (use Summarize tool and group by week). Then you join the sum values to the original using the Join tool. To calculate the percentages, a Formula tool can be use. Last step is "formatting" as percentages, this needs conversion to string datatype and adding a "%" sign. I've attached a sample workflow.
Corrected calculation (percentages of total all columns, not single column)
Best,
Roland
Awesome, this worked perfectly!