Calculate new costs based on BOM and quarter

[NicoSteininger]

Hey experts,

I am fairly new to Power BI and have the following question:

I have 3 tables:

- a BOM, which looks similar to the BOM presented in this thread: https://community.powerbi.com/t5/Desktop/caculating-through-the-BOM/m-p/401268#M183367. However my BOM in addition to that has an additional attribute "Material Type" (e.g. type 1000, 1010, 1050, etc.) next to each position.

- The Material Types are splitted into different raw materials (e.g. Aluminium, Copper, etc.). Each raw material represents a certain percentage which the material type consists of, e.g. Type 1010 is 20% Aluminium, 10% Copper and 5% Gold.

- A Forecast Table which show how the Raw Materials will probably change in price in the next quarters.

Example:

My main product is a computer.

--> BOM: It consists of

- graphics card - Material Type 1000

- monitor - Type 1010

- Keyboard - Type 1010

- mainboard - Type 1050 and so on.

--> 2nd table:

Type 1000 consists of 10% Aluminium (the other 90% are not important, or at least not affected from external price changes)

Type 1010 consists of 50% Aluminium, 10% Copper and 5% Gold

Type 1050 consists of 80% Steel

-->3rd table:

- Aluminium will change

Q2: +10%

Q3: +20%

Q4: +40%

- Copper will change

Q2: -5%

Q3: -10%

Q4: -15%

What I need to do now is to go through each material which the main component (the computer) consists of, check which material type that is, check which raw materials it consists of, calculate the static amount of the cost which is unaffected of price changes for the raw materials, apply the forecasted prices to the raw material percentage and add that all up together.

What I have done so far is trying to calculate the static amount of the cost, which stays unaffected, yet I already failed with this task. This code only works, if I filter for only 1 material type / Warengruppe. As soon as I select every material type it´s not working anymore.

Berechne statischen Anteil = 
CALCULATE (
    SUM ( dBOM[Herstellkosten] );
    FILTER ( dBOM; [Auswahl Kopfmaterial] );
    FILTER ( dBOM; [Auswahl Warengruppe] )
)
    * (
        1
            - CALCULATE (
                SUM ( dWarengruppe_Input[Wert] );
                FILTER (
                    dWarengruppe_Input;
                    dWarengruppe_Input[Warengruppe] = [Auswahl Warengruppe]
                )
            )
    )

If anybody could provide an idea how to actually tackle this task I would be really glad, because I have no idea if I can put a solution into a single measure, if I have to split the task into many measures, or if I need to calculate the values within the tables itself.

Thank you in advance!

[v-juanli-msft]

Hi @NicoSteininger

If your tables are not like mine, we could do some transform for the data.

Best Regards

Maggie

[NicoSteininger]

Hey @v-juanli-msft,

thank you for your fast reply! Fortunately my tables look almost similar. I will show you how my tables look so far:

Table with the BOM:

 

It can consists of many different main materials.

Table with the material types and its respective raw materials:

Looks (I think) similar to yours, although you have 2 additional columns which I don´t really understand why I would need them.

And then there is the third table which looks like this:

Sorry for mixed up English and German stuff, I hope you can still understand what I mean.

I already came up with a vague idea how the final report should look like, but the missing piece is the cost for the total product from the first table, for each quarter. I was able to produce the correct prices via VBA in "normal" Excel, yet here I can´t come up with a working solution, because from my understanding I would need to iterate over every material and so on.

My solution idea looks the following: I created another table where I put the main material + the quarters (which are created dynamically, since the quarters will logically change every time I update it, since it´s always going forward). But now I would need an additional column where I calculated the correct prices for each quarter:

The code for the last table which is created out of a query looks like this:

let
    Quelle = dBOM,
    #"Überflüssige Spalten löschen" = Table.RemoveColumns(dBOM,{"Warengruppe","Material","Bezeichnung","Menge","Einheit","Herstellkosten"}),
    #"Auf Kopfmaterial kürzen" = Table.Distinct(#"Überflüssige Spalten löschen", "Kopfmaterial"),
    #"Quartale hinzufügen" = Table.Combine({#"Auf Kopfmaterial kürzen", Quartalsabfrage}),
    #"Null-Zeilen entfernen" = Table.RemoveLastN(#"Quartale hinzufügen", each [Kopfmaterial]=null),
    #"Spalte Material löschen" = Table.RemoveColumns(#"Null-Zeilen entfernen", "Raw Material"),
    ColumnNames = Table.ColumnNames(#"Spalte Material löschen"),
    #"Ersetzter Wert" = Table.ReplaceValue(#"Spalte Material löschen",null,1,Replacer.ReplaceValue,ColumnNames),
    #"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Ersetzter Wert", {"Kategorie Kopfmaterial", "Bezeichnung Kopfmaterial", "Kopfmaterial"}, "Attribut", "Wert"),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Entpivotierte andere Spalten",{"Wert"}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Entfernte Spalten",{{"Attribut", "Quartal"}}),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Umbenannte Spalten",{{"Kategorie Kopfmaterial", type text}, {"Bezeichnung Kopfmaterial", type text}})
in
    #"Geänderter Typ"

Here´s a picture how the final report should probably look like.

Sorry for the long post, but I tried to describe my problem + my solution ideas as detailed as possible.

Thanks in advance!

Best regards,

Nico

[v-juanli-msft]

Hi @NicoSteininger

Assume your three tables structures as my test, i can get the result as below.

To get the forecast price, i need to know your table structure so i can go on.

Please download my test pbix to see what difference between yours and mine.

Please share your example data with me so i can know how your table structures.

Best Regards

Maggie