Macro to distribute Numbers across groups of rows

[bkreutz0422]

Hello,

I am attempting to create a macro to distribute a selected numeric field  across line items in a data set, with an option to group by certain fields and also weight the distribution of the numbers by another field. I have put together an example of the data below. The green text is what would need to be calculated by the macro

Product (group by)	Days Available (weight)	Revenue (numeric field)	Total Weight (sum of Days Available by group)	Total Revenue (sum of Revenue by group)	Distributed Revenue (Weight/Total Weight)*Total Revenue
A	7	2000	37	2000	378.37
A	30		37	2000	1621.62
B	12	500	30	1500	600
B	18	1000	30	1500	900

I have successfully been able to generate the first two green columns (the total columns) by modifying the Weighted Average tool (which is a macro), but cannot figure out how to create the third column. The problem I am running into is that I need to join the Group By field Total Weight and Total Revenue columns back in with the original dataset by dynamically selected group fields. the Weighted Avg macro uses XML to update the summarize tool with the Group By fields. I am unsure of how to preserve these group by fields for the subsequent Join tool. 

Thanks in advance!

[cjaneczko]

Why do you need to do a group by field. If you have the first two green columns all you need is a formula tool that takes ([Days Available]/[Total Weight])*[Total Revenue]. It looks like you have all the information you need.

[bkreutz0422]

Hi, thanks for the reply.

Yes, this can be accomplished with a formula tool, as I have done in the Alteryx file I provided. But my goal is to build it into a singular macro that is repeatable and used by a whole team of Alteryx users as a single without the need for subsequent steps. The problem I am running into is that the user dynamically selects grouping columns, which need to be preserved for the Join tool in the macro (also attached). My question isn't how to do the number distribution using a formula (I already know how to do this), but rather how to finish the macro to do this for the user with their dynamically-selected grouping fields. 

For reference, the macro output in its current state gives a singular Total Weight and Total Value numbers for each group (see attached screenshot). The output from this needs to be re-joined with the raw data in order to perform the formula tool that spreads the number across the group. This Join step and Formula step also need to be included in the macro, which is where I am having a hard time, as the dynamically-selected grouping fields need to be the Join fields.

Does that make sense? I think if you open up the two attachments, you'll see what I mean.

Thanks in advance.