Forum Discussion
SUMX for data from multiple tables
You must insert the context of the element. Make sure you have a common item dimension
sumx(summarize(Item,Item[Item],"_Avg",Average(Table2[Unit Price]),"_Qty",sum(Table1[QUA Qty])),[_Avg]*[_Qty])
See also this blog, how row context plays a role:https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451
Community ChampionYou will need to create a unique key between the tables to do this effectively. Most often you see something like:
Sales Price =
SUMX(
Sales,
Sales[Units] * Product[Price]
)
The product table would be a DIM table that would have something like a ProductID that would relate to a sales record in a Sales FACT table with the same ProductID. The Product Table only has the ID one time - no repeats. The Sales Table can repeat as many times as you like.
Your warehouse/item combo won't work like that the way it is set up. You should have a single field that defines that item. You could do itin Power Query by creating a concatenation of the Warehouse ID and Product ID, then create a similar field in the Sales table.
In Power BI when you create relationships between tables, those aren't Joins like you would think in a relational database. (They are in the Power Query side, but that isn't where you create measures.) They are filter relationships, and you can only have one active at a time. You cannot even activate 2+ relationships with USERELATIONSHIP(). That will activate an inactive relatinship, but deactivate all others between those two tables.
Hi edhans,
Thank you very much for your response.
I forgot to mention in original query that i have created table 'Item List' with list of unique items from both tables (with Distinct & Union) and are linked to 'Item List' table.
- edhans
Then link that field in the Item table to the Warehouse table with the same unique field as a One-to-Many, Single filter, then use something like:
This Measure = SUMX( Warehouse, Item[Price] * Warehouse[Quantity] )And I am not 100% clear on your goal. If you are trying to get a single visual combining data from both warehouses, that will be difficult. Not impossible (probably) but difficult. Power BI works best with a star schema, which means one FACT table for all simlar data. You seem to have two FACT tables with the same type of data - two warehouse files. That should be modeled as one table in Power Query through an Append query with a new field that identifies the warehouse.