Hello,
I have two data sets that I have already summarized to achieve counts by a particular group. Now I need to see the % difference for total, by particular group and subset of that group. The table below represents the data structure from both datasets, and the counts are different and the fee totals are different. After summarizing, how do I determine the % difference in counts to see who had more fees assigned (by title and title2), and then how do I see the % difference in fee totals?
Name | Title | Title 2 | Fee |
John Doe | Software | Software Leader | 100 |
Santa Clause | Software | Software Leader | 200 |
Jack Kent | Marketing | Marketing Analyst | 100 |
Boo Douglas | Marketing | Marketing Analyst | 150 |
Thanks,
JW
Add additional Summarize Tools after this one. One tool would Group by Title and Sum Fee, the other Summarize Tool would Group by Title 2 and Sum Fee. From there you can do whatever calculations you'd like.
Do want this or someting else
Name | Title | Title 2 | Fee | Result |
John Doe | Software | Software Leader | 100 | Lowest |
Santa Clause | Software | Software Leader | 200 | 100% |
Jack Kent | Marketing | Marketing Analyst | 100 | Lowest |
Boo Douglas | Marketing | Marketing Analyst | 150 | 50% |
What determines the %? Why is 200 = to 100% and 150 only 50%?
@cjaneczko as it is 100% more than the lowest and similar with 50%
100+100%= 200
@cjaneczko
this is to be confirmed by @walkerj7 is he looking for this or something else.
Thanks for all the advice on how to solve this. Just for transparency my output should look like the following:
Title | Title Count within its dataset | Title % of Total (Based on it's dataset) | Title Count Difference between datasets | Average Fee % difference (fee total in dataset a vs fee total in dataset b) |
Marketing (Dataset A) | 20 | 37% | 40% lower than Dataset B | $50 lower |
Marketing (Dataset B) | 35 | 48% | 40% higher than Dataset A | $50 higher |
Then I would do this for Title 2 and in the end my analysis would determine which titles have higher fee counts and which titles have a biggest difference in fee totals.
is this the complete data set for which you have shared the sample output
Going to need a lot more sample data to get the info you need. A sample of Data A and Sample of Data B. But you would need to add quite a few summarize tools, some Unions, some Append tools with Join tools to get to where you need to go. Its all doable, but a larger sample size of both data sets would be good. Its difficult to tell how you got to the 40% and $50 fee without the math that shows it, or sample data that adds up to that 40% and $50. The original samples don't add up to your desired output. Are you comparing the fees and title counts between the two sets against all titles or just same titles?