Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello, please I need some help on the following situation:
I have 3 tables:
SalesOrderTable
OrderNum | Product | Quantity |
ON-01 | P1 | 10 |
ON-01 | P2 | 20 |
ON-01 | P3 | 30 |
ON-02 | P1 | 20 |
ON-02 | P2 | 15 |
ProductTable
Product | Category |
P1 | FinishedProduct |
P2 | FinishedProduct |
P3 | FinishedProduct |
RM1 | RawMaterial |
RM2 | RawMaterial |
RM3 | RawMaterial |
BillsOfMaterialTable
FinishedProduct | RawMaterial | Percentage% |
P1 | RM1 | 0,33 |
P1 | RM2 | 0,33 |
P1 | P2 | 0,33 |
P2 | RM2 | 0,5 |
P2 | RM3 | 0,5 |
I need to explode the BOM Formula with SalesOrderTable products in order to measure FinishedProduct and Raw material quantities, but if we look further, inside P1 we have another finished product called P2, that needs explosion in order to get RM quantities as well, creating another iteration, and so on....
It's not that hard when we have only one iteration, but as we go with more than one, it's getting complicated and memory consuming...
So, what would be the best way to calculate all iterations without creating so many conditions and collumns in order to have all Finished Products quantities and Raw Materials?
Solved! Go to Solution.
Hi @aferreira_adm ,
Here are the steps you can follow:
1. Create measure.
Finished Products quantities =
var _select=SELECTEDVALUE('SalesOrderTable'[Product])
var _sumquantity=SUMX(FILTER(ALL(SalesOrderTable),'SalesOrderTable'[Product]=_select),[Quantity])
var _p1=MAXX(FILTER(ALL('Product Category'),'Product Category'[Product]=_select),[Category])
var _column=SELECTCOLUMNS(FILTER(ALL('Product Category'),'Product Category'[Category]=_p1),"1",[Product])
return
IF(
MAX('BillsOfMaterialTable'[FinishedProduct]) in _column&&MAX('BillsOfMaterialTable'[FinishedProduct])=_select,_sumquantity *MAX('BillsOfMaterialTable'[Percentage%]),0)
Raw Materials =
var _select=SELECTEDVALUE('SalesOrderTable'[Product])
var _sumquantity=SUMX(FILTER(ALL(SalesOrderTable),'SalesOrderTable'[Product]=_select),[Quantity])
var _p1=MAXX(FILTER(ALL('Product Category'),'Product Category'[Product]=_select),[Category])
var _column=SELECTCOLUMNS(FILTER(ALL('Product Category'),'Product Category'[Category]=_p1),"1",[Product])
return
IF(
NOT(MAX('BillsOfMaterialTable'[RawMaterial]))in _column&&MAX('BillsOfMaterialTable'[FinishedProduct])=_select,_sumquantity *MAX('BillsOfMaterialTable'[Percentage%]),0)
2. Result:
If the results do not meet your expectations, can you show the expected results in the form of pictures, how to obtain Finished Products quantities and Raw Materials, we can better help you
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @aferreira_adm ,
Here are the steps you can follow:
1. Create measure.
Finished Products quantities =
var _select=SELECTEDVALUE('SalesOrderTable'[Product])
var _sumquantity=SUMX(FILTER(ALL(SalesOrderTable),'SalesOrderTable'[Product]=_select),[Quantity])
var _p1=MAXX(FILTER(ALL('Product Category'),'Product Category'[Product]=_select),[Category])
var _column=SELECTCOLUMNS(FILTER(ALL('Product Category'),'Product Category'[Category]=_p1),"1",[Product])
return
IF(
MAX('BillsOfMaterialTable'[FinishedProduct]) in _column&&MAX('BillsOfMaterialTable'[FinishedProduct])=_select,_sumquantity *MAX('BillsOfMaterialTable'[Percentage%]),0)
Raw Materials =
var _select=SELECTEDVALUE('SalesOrderTable'[Product])
var _sumquantity=SUMX(FILTER(ALL(SalesOrderTable),'SalesOrderTable'[Product]=_select),[Quantity])
var _p1=MAXX(FILTER(ALL('Product Category'),'Product Category'[Product]=_select),[Category])
var _column=SELECTCOLUMNS(FILTER(ALL('Product Category'),'Product Category'[Category]=_p1),"1",[Product])
return
IF(
NOT(MAX('BillsOfMaterialTable'[RawMaterial]))in _column&&MAX('BillsOfMaterialTable'[FinishedProduct])=_select,_sumquantity *MAX('BillsOfMaterialTable'[Percentage%]),0)
2. Result:
If the results do not meet your expectations, can you show the expected results in the form of pictures, how to obtain Finished Products quantities and Raw Materials, we can better help you
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |