Comparing two excel sheets without merging

[Anonymous]

Hello,

I'm dealing with two warehouses that use different systems for item tracking. I receive weekly reports from each warehouse, and I just need to set up a dashboard that shows the differences. My current measure below partially works, it shows which items are missing, but I need to show the total quantity as well. One of the common columns in both sheets is the item ID, however whereas the sending warehouse records (almost) each item on a separate line, receiving warehouse records them in one line, adding a separate quantity column. To check whether one item is contained in the other I created this measure:

Contained in Table 2 = IF(
CONTAINS(
      'Receiving', 
      'Receiving'[ItemID], MAX('Sending'[ItemID)),
1,
0)

I created a "Quantity" column in the sending table and added "1" to each line. While this above measure shows me which items are not present in the receiving warehouse, it fails to show the number of items and as soon as I add the Quantity column from the receiving table, the visualization breaks. I actually only figured out my mistake when I compared the total numbers and what the measure shows me (54 in my viz, which is clearly wrong, see below).

I can't merge/append these two tables for various reasons. Is there a way of creating a better measure that shows the differences in both tables as well as total number of items that do not appear in each tables? Any other suggestions?

Below are two example tables:

Sending

ItemID	Description	Item Type	Quantity (Custom Created)	QTY (Some special items have a quantity line)
6570	PowerBi Desktop	Software	1
6570	PowerBi Desktop	Software	1
6575	MS Office	Software	1
6890	Chair	Office Tools		1

Receiving

ItemID	Description	Item Type	Quantity
6570	PowerBi Desktop	Software	2
6575	MS Office	Sofware	1

[PaulDBrown]

You could create a new table to identify the rows which are not in the other table using something along the lines of:

Table differences =
VAR receiving1 =
    SELECTCOLUMNS (
        Receiving,
        "ItemID", Receiving[ItemID],
        "Description", Receiving[Description],
        "Item Type", Receiving[Item Type],
        "QT", Receiving[Quantity]
    )
VAR sending1 =
    SUMMARIZE (
        Sending,
        Sending[ItemID],
        Sending[Description],
        Sending[Item Type],
        "QT", SUM ( Sending[Quantity (all)] )
    )
VAR OnlyRec =
    ADDCOLUMNS ( EXCEPT ( receiving1, sending1 ), "From table", "Receiving" )
VAR OnlySend =
    ADDCOLUMNS ( EXCEPT ( sending1, receiving1 ), "From table", "Sending" )
RETURN
    UNION ( OnlyRec, OnlySend )

Which will get you this:

I've attached the sample PBIX file for your reference

[Anonymous]

Thanks @PaulDBrown 

This is truly impressive. You've really shown your expertise on PBI. 

However, in my test, I found if in the receiving table they recorded 1 item per 1 line (they have done that sometimes), the difference table considers this as only 1 in quantity. See below, the item actually appears in 7 different lines in the receiving table, so technically it shouldn't appear on this table at all (There are 7 items with the ID 6908 in both tables).

When I show the QT column as a sum, the table shows the correct number, but this item shouldn't be here at all. See below:

Any suggestions?

Thanks, 

[PaulDBrown]

In that case you need the SUMMARIZE function in the first VAR too.

Table differences =
VAR receiving1 =
    SUMMARIZE (
        Receiving,
        Receiving[ItemID],
        Receiving[Description],
        Receiving[Item Type],
        "QT", SUM ( Receiving[Quantity])
    )
VAR sending1 =
    SUMMARIZE (
        Sending,
        Sending[ItemID],
        Sending[Description],
        Sending[Item Type],
        "QT", SUM ( Sending[Quantity (all)] )
    )
VAR OnlyRec =
    ADDCOLUMNS ( EXCEPT ( receiving1, sending1 ), "From table", "Receiving" )
VAR OnlySend =
    ADDCOLUMNS ( EXCEPT ( sending1, receiving1 ), "From table", "Sending" )
RETURN
    UNION ( OnlyRec, OnlySend )

try that and see if it works.

BTW, since this is table, you can create measures for example to calculate the balance by row as a summary

[Anonymous]

Amazing! Thanks @PaulDBrown 

One final question, in my sending table I have a "Status" column that shows the item's current status. Do you have a recommendation for me to integrate it to this table differences, so it ignores items that has the status "In transit"?

Thanks, 

[PaulDBrown · Accepted solution]

You have to filter out the rows containing "in transit":

try:

Table differences =
VAR receiving1 =
    SUMMARIZE (
        Receiving,
        Receiving[ItemID],
        Receiving[Description],
        Receiving[Item Type],
        "QT", SUM ( Receiving[Quantity])
    )
VAR sending1 =
    SUMMARIZE (
        FILTER(
        Sending, Sending[Status] <> "In transit"),
        Sending[ItemID],
        Sending[Description],
        Sending[Item Type],
        "QT", SUM ( Sending[Quantity (all)] )
    )
VAR OnlyRec =
    ADDCOLUMNS ( EXCEPT ( receiving1, sending1 ), "From table", "Receiving" )
VAR OnlySend =
    ADDCOLUMNS ( EXCEPT ( sending1, receiving1 ), "From table", "Sending" )
RETURN
    UNION ( OnlyRec, OnlySend )

[Anonymous]

Awesome! Thanks @PaulDBrown It works marvelously! 

I hate to be that guy, but do you have a more elegant solution than what I have, regarding the differences per item? I created 3 measures and used a matrix viz to show difference per item, so it shows 1 and -1 for the below two rows:

Here are the measures: 

Sum Sending = CALCULATE(SUM('Table differences'[Quantity]), 'Table differences'[Source] = "Sending")

Sum Receiving= CALCULATE(SUM('Table differences'[Quantity]), 'Table differences'[Source] = "Receiving")

Sum Differnece = [Sum Sending] - [Sum Receiving]

Obviously when I do that, I get an annoyingly ugly result:

If you have a better solution, I would be happy to learn.

Thanks,

[PaulDBrown]

I take it the "QT" are columns from the table. If you are using a matrix, use measures instead (leave the column bucket empty),  or try a table visual instead of a matrix.
You can also "hide" a column in a visual by dragging the boundary, but you need to turn off word-wrap and maybe auto-width in the formatting pane.

[Anonymous]

You're a savior! Many thanks!