Pro-Rata Percentage Values Needed calculated for Many Columns Dynamically

[taxguy33]

Hi All,

We have 239 cost centers with Direct Labor amounts that are going to be part of various Cost Driver groupings (i.e., a US cost center would be part of the Cost Drivers groupings - US DL, US Eng DL, and Worldwide DL, etc.). I have various formulas within a formula tool determine a True/False flag to determine if each cost center should be part of each Cost Driver grouping (i.e., is it  part of US, is it part of ENG or not, etc.). After I determine inclusion viability, I bring over the Direct Labor amount to those columns and that is where I get lost. 

I would then like to subtotal each of those new Cost Driver columns (I think I have this part being done correctly), but I believe I want to bring these subtotals back into the Data Flow and do a pro-rata percentage calculation for each cost center that is part of each Cost Driver column grouping (currently using an Append, but could be wrong). I would then remove the subtotals as I am just looking for %'s at the end of the day.

For example, if Cost Center 10004 is part the US DL Cost Driver grouping, I would want it's 409,277.35 relative DL amount divided by the US DL Cost Driver grouping Subtotal of 588,729,151.52 and the result would go ideally replace the $409,277.35 amount in the "US DL" column in the CC10004 row as 0.000695187. So on and so forth for every match of a Cost Center and a Cost Driver grouping. 

Can someone point my workflow in the right direction on how to get there from where I've gotten thus far?

Thanks!

[RolandSchubert · Accepted solution]

Hi @taxguy33 ,

 I think, it's easier to use a Join approach to create a list of all valid combinations of cost centers and cost drivers. I've modified the "calculation part" of your workflow a bit - what do you think? Let me know, if it works as expected.

Best,

Roland