Backstory:
We have an MRP system that provides me with two tables, one spits out the amount of each material consumed for a given run (ex. 10,000 lbs of Material A, 3,500 lbs of Material B, etc...) and another table that spits out the total run weight and number of batches (ex. 50 executions/batches (@ 1000 lbs), 50,000 lbs of Product A). What I am trying to do is determine the percentage composition (on average) of all of our materials across each production line and be able to spot unfavorable trends (due to the nature of our products, the composition changes for a variety of reasons, so Product A won't always be 74% Material A).
I would then like to be able to create a table or graph of this data and using a splicer, be able to toggle between views of the different materials, so I wouldn't like material to be "baked" into the DAX formula (as it would create way too many columns/measures with all our formulas, lines, and plants).
Problem/Attempted Solution:
What I did was try to generate a formula dividing Table 1 (Consumption of each material) by Table 2 (Total Consumption):
Percent Composition = DIVIDE('Table 1'[Quantity], SUM('Table 2'[Quantity]))
I then applied a filter to the table or visual for production, but it wasn't filtering all the data, only those used to calculate the numerator. For example, if I applied a Line 1 filter, the numerator would be filtered for that, so it would sum all of Material A used during that date range on Line 1, but divide by ALL of the material used in the entire plant, leaving me with a very small, and incorrect, material composition for Material A.
I then looked on line and thought the CALCULATE function may be the way to go. So I tried:
Percent Composition = DIVIDE('Table 1'[Quantity],CALCULATE(SUM('Table 2'[Quantity], 'Table 2'[Line] = "L1'))
Instead of very small numbers, this gave me values over 100%...
I then tried swapping the Table 2 instances in the denominator for Table 1, because the sums should be equivalent, but I got equally bizarre results.
Any thoughts?
