How can I compare values from 2 different sheets in Power BI?

[CarlosV]

Hello everyone

I have 2 table sheets in which they share common data and I would like to compare the difference between them to calculate the delta or differences with a filter condition. 

An example of this table would be as follows:

TABLE 1                                                     TABLE 2                                                    DELTA

STATUS   PART NUMBER     QTY                STATUS    PART NUMBER     QTY       STATUS  PART NUMBER  QTY

OPEN        78124                 100                 OPEN         78124                  200         OPEN       78124             300     (100+400 - 200) 

OPEN        78125                 100                 OPEN         78125                  300         OPEN       78125             200

OPEN        78112                 300                 OPEN         78112                  300         OPEN       78112               0

CLOSE       78115                 400                 OPEN         78115                  350         OPEN        78115             150

CLOSE       78118                 500                 OPEN         78118                    80         OPEN        78118           520

OPEN        78115                 500

OPEN        78118                 600 

OPEN        78124                 400

In this example the comparison is only for the Open Status Condition, and it is between each part number of each table.

For Part number  78124 in table 1 it is repeated and the total is 500, and for table 1 part number 78124 in table 2 is 200, so if the difference is compared is equal to 200.

So at the end what I am looking for is to have a summary of quantities difference between table 1 and 2 with open status and per part number.

It kinds of messy if I miss information please let me know

I will appreciate any advice to create this table comparisson in power BI.

Thanks in advacne

Best regards

Carlos V

[mahoneypat · Accepted solution]

Try this approach.  Make a table visual with the Part Number from Table1 and add this measure

Delta =

var currentpart = selectedvalue(Table1[PartNumber])

var table1open = calculate(Sum(Table1[Qty]), Table1[Status]="Open")

var table2open = calculate(sum(Table2[Qty]), Table2[Status]="Open", Table2[PartNum]=currentpart)

return table1open-table2open

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[CarlosV]

Thank you, this did the job, I was breaking my head, thank you again for the advice.

Best regards!

Carlos V