Forum Discussion

KatkaS's avatar
KatkaS
Icon for Post Patron rankPost Patron
6 years ago
Solved

Vlookup and calculation

Hello,

I'm struggling with following:

I have two tables and I need to calculate the difference between amounts in two columns (one in each table),

based on the FAM line (4+ digit number).

 

Could you please advise what could be the solution?

Thank you very much!

Need Help

17 Replies

  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    6 years ago

    Hi.

     

    • Load both tables into Power Query.
    • Prepare your key column.
    • Merge the Queries
    • Calculate the difference

    If I answered your question, please mark my post as solution, this will also help others.

    Please give Kudos for support.

    • KatkaS's avatar
      KatkaS
      Icon for Post Patron rankPost Patron
      6 years ago

      Thank you for your support!

      But I don't understand how merging could help, perhaps I'm missing something? One column is the common information for both tables and I would need to calculate if there is a difference in the amounts based on that information and operational company.

      I tried to explain myself better in detail below.

      Thank you for checking this!

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    You can bring the field from one table to another like this if needed

     

     

    Max customer = MAXX(filter(Sales,Sales[Order Id]='Item'[Max Sales order id] && Sales[item_id]='Item'[Item ID]),Sales[Customer ID])

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks.

    My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

     

    • KatkaS's avatar
      KatkaS
      Icon for Post Patron rankPost Patron
      6 years ago

      Thank you, Amitchandak!

      I was not clear enough, I think, could you please check additional information I just posted with screen print?

      Thank you very much for your time!

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi KatkaS ,

     

    If you don't mind, please give me some data sample.


    Best Regards
    Icey

    • KatkaS's avatar
      KatkaS
      Icon for Post Patron rankPost Patron
      6 years ago

      Hello Icey!

       

      Thank you for your time!

      I have two tables and I have to find out that when I deduct the amounts from each other, if I get a zero difference or there is a difference (and how much). The common thing it the Fam line (it is an accounting line) and Company abbreviation. I also need to be able to filter based on Period (I usually have Slicer for this).

      The outcome should be Matrix: Company / FAM line / Difference...

       

       

      • Icey's avatar
        Icey
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi KatkaS ,

        It would be great if you could provide your sample data in Excel.

         

        Best Regards,

        Icey

    • KatkaS's avatar
      KatkaS
      Icon for Post Patron rankPost Patron
      6 years ago

       So in my example above I search for the amounts to compare linked to FAM line (column C) and number in column B (Balance sheet). It must be based on the Operational company (CZC in this case, but there are many more). FAM lines are the same for all the companies.

       

      I hope I'm clearer now..

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi KatkaS ,

    Try this:

    1. Change the data type of '2nd source SAP'[FAM Line] to 'Text'.

    2. Create columns in '2nd source SAP'.

     

    Amount of 1st = 
    CALCULATE (
        SUM ( '1st source ECS'[AMOUNT] ),
        FILTER (
            '1st source ECS',
            [BALANCE SHEET + INCOME STATEMENT - Copy.1.2]
                = EARLIER ( '2nd source SAP'[FAM Line] )
                && [PERIOD] = EARLIER ( [PERIOD] )
                && [FAM] = EARLIER ( '2nd source SAP'[Operational Company] )
                
        )
    )
    Difference = '2nd source SAP'[YTD]-'2nd source SAP'[Amount of 1st]

     

     

    Best Regards,
    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • KatkaS's avatar
      KatkaS
      Icon for Post Patron rankPost Patron
      6 years ago

      Hello Icey,

      I'm really sorry to take your time, your solution looks great, but it isn't working for me..

       

      I Added Custom Column as per your advice, but I'm getting below error:

       

       

       

       

       

       

      When I added the columnm there was no syntax error (but I had to remove ' ' signes and doubled &s, they were giving errors....), here how it looks like:

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Do you think you could still have a look in this? Thank you!