I’m working on a planning report.
In the report, a certain amount of each yarn goes into an Article which has a “Color” and a “Size” as a context
The “Value” is the forecast for that Article/Color/Size by month in metres (in this case August).
So the DAX is as followed for each variable:
Kg / m Needed:
[Kg of all Yarns for a Metre] * [% Yarn]
[Kg of all Yarns for a Metre] =
SUM( ‘Yarn BOM’[QTY]),
ALLEXCEPT( ‘Yarn BOM’, ‘Yarn BOM’[Article ID],‘Yarn BOM’[Size ID],‘Yarn BOM’[Color ID])
% Yarn = SUM( ‘Yarn BOM’[QTY]) / [Kg of all Yarns for a Metre] )
KG of Yarn Needed = SUM(Forecast[Value]) * [% Yarn]
In this context, all works as intended and the KG of yarn needed is correct. Note the yarn sums aren’t together, which is what I need…
so when I change the context to this:
Where the yarn proceeds the values with no duplicates, or rather removing the Article/Size/Color context that existed in the previous picture.
The numbers are way to high and I can’t seem to figure out how to get the actual yarn totals required to fulfill the forecasts for Article/Size/Color.
Please assist if possible.
Here is the model if needed; it’s a Many to Many on a customer key (Article & Size & Color ).
There isn’t a way to make it anything other than a many-to-many that I know of because of the repetition in the months and the rows in the BOM having multiple yarns therefore multiple key instances.