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

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's avatar
PaulDBrown
Community Champion
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 )
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

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

        You're a savior! Many thanks!