BOM ( Manufacturing Bill of Material ) totals is incorrect for final assembly cost

[Unni]

BOM sucess .pbix (598.7 KB)

Hi,

Attached BOM of an assembly part with qty and price joined in the data model , i am unable to get the total right please advise how to resolve this

The hierarchies were flattened using the M code provided through the below link

Dynamically flatten Parent-Child Hierarchies in DAX and PowerBI – The...

[Unni]

Appreciate if we can have some BOM related content in Enterprise DNA as these are common cases in any manufacturing organization

[DavieJoe]

What value would you expect to see as the total?

How To Fix Matrix Totals In Power BI

[AntrikshSharma]

@Unni What’s a BOM?

[DavieJoe]

Bill of Materials

Manufacturing bill of materials

[Heather]

While in my world, this is “Block of the Month” (for a quilting group)… very important to define business acronyms, particularly in a forum that reaches out to people internationally

[Unni]

@DavieJoe thanks for pointing me to this direction it seems tough for my level but will try to get this to work

[BrianJ]

@Unni ,

I started to work through a specific solution on this, but realized that I think you need to go back through some of the basic coursework first. Here are some of the problems I’ve identified:

1. Your data model is not correct - you are using the Invoked Function table, but it is not connected to the model by any relationship.
2. You are using implicit, rather than explicit measures, which in general is not a good practice and will not work for measure branching, as is needed here
3. General model cleanup - unnamed table and columns make it very difficult to figure out what’s going on in the model
4. Need to apply fix totals pattern

Here are specific videos I would recommend:

https://forum.enterprisedna.co/t/quick-measure-feature-positives-vs-negatives/4885

https://forum.enterprisedna.co/t/why-your-total-is-incorrect-in-power-bi-the-key-dax-concept-to-understand/5108

https://forum.enterprisedna.co/t/measure-branching/5962

Data Transformations & Modeling

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

I hope this is helpful.

• Brian

[Unni]

Thanks @BrianJ for the time you have taken, i will go through lessons recommended , clean the model & measures and try to get it right.

[BrianJ]

@Unni,

Glad to help. If after you get those things squared up, if you’re still having problems getting the totals to reconcile, just give a shout and I’ll be glad to work through it with you.

• Brian

[EnterpriseDNA]

Hi @Unni, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[Unni]

Hi , yes the query was solved the intial pbix demo data was confusing hence made another demo model using Forum Topic Fix incorrect totals by Greg. Attached the excel source file & pbix
BOM demo cycle.pbix (49.3 KB)
BOM demo Dataset .xlsx (11.8 KB)