Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
hi,
I have 3 tables:
1. Product Master data as dimension table for all material code
2. BOM: 1 Material contains many child components
3. Sales Order table: as fact table for orders of each parent material codes
--> what is the approach for calculating child components requirement from the Sales orders?
Material requirement of Purchased Item = Requested Quantity (from Sales orders) x Usage (from BOM table)
Thanks,
Solved! Go to Solution.
hi,
I found the solution through Many to Many relationship topic.
https://exceleratorbi.com.au/many-many-relationships-dax-explained/#comment-23150
thanks for helping anyway.
@Iamnvt,
Please create a measure in the BOMReport table using DAX below.
Material requirement of Purchased Item = SUM ( 'BOMReport'[Usage] ) * SUMX ( RELATEDTABLE (OB), OB[Requested Quantity] ) )
And you can review the example in my environment.
Measure = SUM ( 'product'[price] ) * SUMX ( RELATEDTABLE ( sales ), sales[amount] ) )
However, if you don't get expeted result using the above DAX, please share sample data of your tables. Do mask sensitive data before uploading sample data.
Regards,
Lydia
hi,
thanks for your answer. It seems not working.
I attached a sample with the expected result. This can be done through merge function in PQ, however, I don't want to flatten the material requirement in BOM table.
https://1drv.ms/x/s!Aps8poidQa5zku5ZWW9lT_x6em_EZA
Just want to find a DAX solution to calculate the component requirements.
Thanks again,
@Iamnvt,
Create a new table using DAX below.
Table = GENERATEALL(BOM,var productid =BOM[Product] return SELECTCOLUMNS(CALCULATETABLE(Sales,Sales[Product]=productid),"sales",Sales[Sales]))
Then create a table visual as follows.
Regards,
Lydia
hi,
I found the solution through Many to Many relationship topic.
https://exceleratorbi.com.au/many-many-relationships-dax-explained/#comment-23150
thanks for helping anyway.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
105 | |
104 | |
89 | |
65 |