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,

[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

[BrandonB · Accepted solution]

Workflow attached!

 

[benakesh · Accepted solution]

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 .