Forum Discussion
Link Production BOM Table to Production Table
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
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 Produced | Required Item | Quantity Required |
| RM012 | RM001 | 1 |
| PA002 | RM001 | 1 |
| PA001 | RM001 | 1 |
They all require the same Raw Material,"RM001".
Here is a sample table for their production record, filtered by single date.
Item Produced | Manufactured | Date |
| RM012 | 943.6 | 01/01/2023 |
| PA002 | 8492.4 | 01/01/2023 |
| PA001 | 895.8 | 01/01/2023 |
| PA002 | 2500 | 01/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.