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
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.
Community ChampionThen 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.