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
See image:
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] =
CALCULATE(
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:
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.
Hi @cmayers, welcome back to the forum! We aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Hi @cmayers, to receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.
Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Including all of the above will likely enable a quick solution to your question.
Hi @cmayers, we’ve noticed that no response has been received from you since the 2nd of November to upload the requested file demo, images of the entire scenario you are dealing with, screenshot of the data model. We just want to check if you still need further help with this post? In case there won’t be data reference receive in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please create a new thread for better visibility. Thanks!
This is an interesting problem, and I would be happy to assist you with it. However, I will really need a PBIX file in order to do so, particularly given the use of the bidirectional relationship since that can produce irregular and unexpected results.
If your data contains confidential information, please refer to the video below for simple techniques on how to anonymize it so that it can posted to the forum.
None of this data is up-to-date so I’ll give you a copy.
Also I feel working outside of the boundaries of the example may harder to understand, maybe(?)
Plus multiple columns work as keys etc.
I hope I explained it well enough above but if not then please let me know.
Also, for the drillthrough, the kg of yarn needed is correct, it’s only incorrect when it get summarized as a whole instead of having the article, size etc context.
Thanks very much for providing the PBIX file and the additional explanation - both make it easier for us to provide you quality support. I need to call it a night tonight, but will dig in on this in detail tomorrow and provide you a solution ASAP.
I’ve been working on this one for a while, and I am convinced that the solution will rest on reworking this data model. The key I believe will be coming up with a way to uniquely identify each record within Yarn BOM. I’ve been playing around with different options, but realize I don’t understand the details of your data well enough to make that work. Can you please tell me what the minimum fields would be that we could concatenate together to uniquely identify a row?
Once we have that, I think we will be able to use the elements of Yarn BOM to filter Forecast in a one-to-many relationship and then also filter Forecast by a proper date table, since filtering currently by only month will blow up once the data begins crossing years
Also, can you please provide a mockup, perhaps using Excel, to illustrate the final result you want to achieve here. The thing I’m having trouble understanding is what you’re looking for when you remove the current yarn attribute contexts in the matrix? If you’re only looking for total yarn by month, the matrix will collapse to a single row summing over all yarn attributes, which is not what I think you’re looking for (in other words, I’m not understanding why you would remove the current row context from the matrix?).
I hope these questions make sense. If they’re not clear please let me know.
As I said prior – interesting problem, but I think once we get the data model squared away the DAX issues will be much simpler to resolve.
Thank you for taking a look at the problem.
I ended up just appending the queries and running the calculations as fields within the table instead of trying to do the dax for it.
Specifically:
Full KG Total =
CALCULATE(
SUM( ‘Yarn BOM’[QTY]),
ALLEXCEPT( ‘Yarn BOM’, ‘Yarn BOM’[Article ID],‘Yarn BOM’[Size ID],‘Yarn BOM’[Color ID],‘Yarn BOM’[Forecast.Month Sort.Month], ‘Yarn BOM’[Forecast.Planning Year], ‘Yarn BOM’[Forecast.Merged] )
)
Percentage Yarn =
VAR DividedSum = DIVIDE( ‘Yarn BOM’[QTY] , ‘Yarn BOM’[Full KG Total] )
RETURN
IF( DividedSum = 1, BLANK(), DividedSum )
KG / M =
‘Yarn BOM’[Full KG Total] * ‘Yarn BOM’[Percentage Yarn]
Kg Needed =
‘Yarn BOM’[Forecast.Value] * ‘Yarn BOM’[KG / M]
And then ran DAX with the yarn context to get the amount of kg needed by yarn:
KG of Yarn Needed =
VAR TotalKG = SUM( ‘Yarn BOM’[Killograms Needed] )
VAR RESULT = CALCULATE( TotalKG, ALLEXCEPT( ‘Yarn BOM’, ‘Yarn BOM’[Full Yarn Name],‘Yarn BOM’[Forecast.Month Sort.Sort] ) )
Thanks very much for providing the PBIX file and the additional explanation - both make it easier for us to provide you quality support. I need to call it a night tonight, but will dig in on this in detail tomorrow and provide you a solution ASAP.