Forum Discussion

Victorsam's avatar
Victorsam
Frequent Visitor
3 years ago

Link Production BOM Table to Production Table

Hello,
I would like to know how much Raw Materials were used up to produce a certain item over a certain period of time. I have two tables, the date series table for production ('Prod5') and the Production BOM table ('BOM4'). Check images for sample data.

BOM table SampleProduction table SampleActive Relationship

Note: I have created a column in the BOM table that shows total materials used for a certain item. 

 

Actual Used = BOM4[Quantity Required]*CALCULATE(
    SUM(Prod5[Manufactured]), 
    FILTER(Prod5, BOM4[Item Produced]=Prod5[Stock ID]))

 

In my visuals, how can I show value for ony the selected dates from the Prod5 table, say "Materials used in making YOG001 between  01/01/2023 and 10/05/2023?"

Need Help
Show and Tell

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Victorsam ,

    It seems that what you are creating is a calculated column in the table 'BOM4', the calculated column will not change according to the user interaction(slicer, filter, column selections etc.) in the report as its value is computed during data refresh and uses the current row as a context... Please review the following links about the difference of calculated column and measure...

    Calculated Columns and Measures in DAX

    Calculated Columns vs Measures

    You can create a measure as below to replace the original calculated column and check if it can return the expected result...

    Actual Used =
VAR _selitemproduced =
    SELECTEDVALUE ( BOM4[Item Produced] )
RETURN
    SUM ( BOM4[Quantity Required] )
        * CALCULATE (
            SUM ( Prod5[Manufactured] ),
            FILTER ( Prod5, Prod5[Stock ID] = _selitemproduced )
        )

    If the above one can't help you, please provide some raw data in your table  'BOM4' and 'Prod5' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

     

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

    • Victorsam's avatar
      Victorsam
      Frequent Visitor
      3 years ago

      Hi Anonymous 
      Thank you for your response. I had not yet found any help until you responded. I created a measure in my 'Prod5' table using the DAX you suggested and it worked, but not how I expected it to. When I filter just a single [Item Produced], it displays the result in my visuals as expected. However, if I filter two or more [Item Produced] that use the same raw material, [Item Required], the visual goes blank.

      Here is a BOM sample table for 3 items. "RM012", "PA002", and "P001".

      Item ProducedRequired ItemQuantity Required
      RM012

      RM001

      		1
      PA002RM0011
      PA001RM0011

      They all require the same Raw Material,"RM001".

       

      Here is a sample table for their production record, filtered by single date. 

       

      Item Produced

      		ManufacturedDate
      RM012943.601/01/2023
      PA0028492.401/01/2023
      PA001895.801/01/2023
      PA002250001/01/2023

       

      In my slicer, when I select [Item Produced] "PA002" on [Date] "01-01-2023", my visual for "Raw Materials Used" shows the value as "10992". Which is an accurate result from the DAX.
      Single Select

      However, If I select "PA002" and "PA001", the visual for RM Used goes blank. The expected result here being an increase in column height and a new data value of "11,888.2". (Total value for "PA002"+"PA001".)
      Multiple Selection

      I have tried an alternative DAX ,

       

      Actual Used = SUM(BOM4[Quantity Required])*CALCULATE(SUM(Prod5[Manufactured]), ALLSELECTED(Prod5[Stock ID])
)

       

      But the result is multiplied by the number of items seleced. If I select "PA001" and "PA002" it gives the result as "23776" which is {actual result*2},["11,888.2"x2]. If I select all three "RM012", "PA002", and "P001", the result is "38495" which is {actual result*3},[12831x3]. I've been exploring other solutions almost all day. Being new to Power BI and data analysis in general, this goes down as my first ever head scrather.

      Your response will be much appreciated.