I have a database of sales and returns for each department of each branch. I am responsible for the data for only one branch, so I can filter the table to only show that one. The columns I have are:
Fiscal Year (FY); Branch (BR); Department (Dep); Sales (S); Returns (R)
Based on these columns, I can filter Branch to just mine (eg Branch A), show the % Returns for each department (R/S %) , and overall Branch returns (Fiscal Year subtotal of R/S). However, the sales of some departments are much much larger than others. So I want to show the impact of each department on the overall Branch returns year-on-year. So what I need is to divide the "Returns" column by the Fiscal Year subtotal of "Sales".
Sample Table: https://i.imgur.com/gHmQ81E.png
The pink column is what I want to dynamically calculate, right now it is manually done by dividing the green column by the hard coded number 62912. This does not work for year-on-year trend since the blue number will change for each fiscal year. Ultimately, I want to plot these on a stacked column chart to show Branch overall % Return and each dept's relative contribution to it, similar to this: https://i.imgur.com/6cJs0OY.png.
Update: I figured it out with some googling. In case it helps someone else out, here is the method I used:
Weighted_Returns = DIVIDE('Table'[Returns], CALCULATE(SUM('Table'[Sales], ALLEXCEPT('Table'[Branch], 'Table'[Fiscal Year]))))
