ไม่สามารถเล่นวิดีโอนี้
ขออภัยในความไม่สะดวก
Use SUMX and RELATED Function in Power BI to Multiply Values from Different Tables in the Model
ฝัง
- เผยแพร่เมื่อ 25 ส.ค. 2024
- This video shows how to use SUMX and RELATED Function when you need to multiply values from one table with values from another field in another table in the data model. This is similar to doing SUMPRODUCT in Excel yet, the columns are from different queries.
How to connect files and build data model: • Connect Files to Power...
Hi, thats great video but I haven't found exactly what I need yet.
My example is that I have table1 with list of Materials and their sold qty, however the same material in that table is visible many many times, because that table is on Outbound delivery Item level, and it shows where it was shipped from and where it was shipped to etc, so later in the visual I would like to be able to filter on those.
Then in the table2 I have Bill Of Material data, so all components of each material. Here also per one Material there can be multiple different components, thus the "Material" also is in multiple rows here in the same column.
So the relation of Material between table1 and table2 is many to many.
I do have table3 with only list of materials so to make One to Many relation to Table1 and to Table2.
Now the measure I would like to get is a multiplication of Material Sold Quantity and the weight of Component.
in the end in the visual I would like to see how much weight of each component type have been shipped from or delviered to certain country.
So if I understand this correctly, and if this would have been in Excel, you would need to do
x = SUMIFS of Weight: Component-Material
y = SUMIFS OrdQty: Material
Then multiply the two?
@@ExcelInsights that is correct.
Then in the visual I would like filter, on e.g. Destination country, and on Component Type.
So I could see Weight of Wood or Plastics, delivered to e.g. France, and then it should show me only this.
As far as I can visualize it, this is how I can help you. Here is a video on how to create a SUMIFS in Power BI: th-cam.com/video/MZEWsQYgFhw/w-d-xo.html
bad video, too complicated, just make easy example for 2 or 3 rows only
Will the formula change if it is just 2 rows or 3 rows?