Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Comparing two excel sheets without merging

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

 

ItemIDDescriptionItem TypeQuantity (Custom Created)QTY (Some special items have a quantity line)
6570PowerBi DesktopSoftware1 
6570PowerBi DesktopSoftware1 
6575MS OfficeSoftware1 
6890ChairOffice Tools 1

 

Receiving

 

ItemIDDescriptionItem TypeQuantity
6570PowerBi DesktopSoftware2
6575MS OfficeSofware1
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    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 )

8 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        PaulDBrown
        Community Champion
        5 years ago

        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