SUMX for data from multiple tables

[Anonymous]

Hi,

I have two table from different sources, one having quantity and another having unit price something like below.

Below is similar to what we receive from warehouse, which would not have unit price and needs to be filtered only for rows where QUA Qty <>0, meaning items in Quarantine

Warehouse Name	Item	QUA Qty
Warehouse A	Item 1	0
Warehouse A	Item 2	10
Warehouse A	Item 3	5
Warehouse B	Item 4	30
Warehouse B	Item 5	25
Warehouse B	Item 6	15

Below table looks similar to what we have in ERP system. ERP system has warehouse number and i have seperate mapping table in PowerBI to map warehouse number and name, which is one to one, meaning one warehouse name can be mapped to only one warehouse number and viceversa.

Also in ERP we have project details and unit price for item may differ for each project within same warehouse, like Item 2 and Item 5.

Warehouse Number	Item	Project	Unit Price
236100	Item 1	Project A	$13.00
236100	Item 2	Project B	$15.00
236100	Item 2	Project C	$12.00
236100	Item 3	Project C	$10.00
236101	Item 4	Project D	$15.00
236101	Item 5	Project E	$26.00
236101	Item 5	Project A	$30.00
236101	Item 6	Project F	$32.00

Now i am trying to get a table with two columns viz,. warehouse name and total price of item in QUA (quarantine) for this warehouse.

Warehouse A	(10 (qty of Item 2 from warehouse table) * AVG($15.00,$12.00) from ERP table) + (5 * AVG($10.00)) + ....
Warehouse B	(30 * AVG($15.00)) + (25 * AVG($26.00,$30.00)) + (15 * AVG($32.00)) + ....

Please help.

[amitchandak · Accepted solution]

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-Differ...

[Anonymous]

Hi @amitchandak ,

Summarize did work and i achieved what i was expecting.

Thank you veru much.

[edhans]

You 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. 

[Anonymous]

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.