Hello- I am working on a dashboard that rolls up monthly raw material rate impacts vs. prior month and vs. prior year. The main data table with the results is made up of raw material codes with their respective reporting categories. The table also contains all historical monthly versions back to the beginning of the year so I can track cumulative monthly rate impacts throughout the year. In a second table I have 10 divisions that have assigned allocation percentages based off of the reporting cateogory in a matrix format (divisions along the row 1 of the spreadsheet and reporting categories in column A) that I unpivoted to contain a column for Category, Division and allocations). I would like to be able to report out on the impact each Division receives of the overall monthly company impact. For example if in September we were $100k favorable vs. prior month due to decreased rates on flour raw materials, i want to be able to show that Division A receives 20% of the impact from any raw material code categorized as Flour, Division B receives 50% and Division C receives 30% (etc for all raw materials and their categories). I just need to be able to drill down all the way to the raw material level or be able to view it at the highest level just by division impact to all divisions. Is this possible to do? I do have an active relationship between my main data table and the allocations table based off of reporting category.
