Divide cell value based on specific criteria

Mostafa_Anwar

Hi All,

I am struggling on how i achieve this, Attached are Sales and Plan dataset and i want to achieve the below:

- After joining Sales and Plan sheets, I want to divide equally the Plan value based on Sales Rep and month,

So the figure should be like below picture

Thanks in advance!

Regards,

Sales Data.xlsx

Plan Data.xlsx

Workflow

Accepted answers

BrandonB

Workflow attached!

sales plan.yxmd

benakesh

Hi @Mostafa_Anwar ,

I am getting different result for one group . You may need to check your expected result for this group .

I used find and replace. To use find and replace you need 'string keys' .

Process: Create keys for both data sets , Summarize to get counts for each group and divide plan by count .

divide_cell.yxmd

All comments

BrandonB

First add a summarize tool to your sales data and group by rep, group by month, and count the sales column. This will output the rep, month, and number that the plan value needs to be divided by. Then join this to your plan data and configure the join tool to join on rep and month. Now you will have the plan value and count of sales rows in the same data set. Next use a formula tool to divide plan by the count. This will result in the divided count value that you are looking for. Finally, use another join tool to connect this final data set back to your original sales data joined on rep and month and you should have the data set you are looking for!

Mostafa_Anwar

Thanks @BrandonB for your respond, Is it possible to attach the workflow of this solution

benakesh

Hi @Mostafa_Anwar ,

I am getting different result for one group . You may need to check your expected result for this group .

I used find and replace. To use find and replace you need 'string keys' .

Process: Create keys for both data sets , Summarize to get counts for each group and divide plan by count .

divide_cell.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703