cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KatkaS Regular Visitor
Regular Visitor

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!

vlookup.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Vlookup and calculation

Hi @KatkaS ,

Please create the columns in Data View or Report View, not Power Query Editor. These are DAX expressions. 

 

Best Regards,
Icey

View solution in original post

17 REPLIES 17
mwegener Super Contributor
Super Contributor

Re: Vlookup and calculation

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.

amitchandak Super Contributor
Super Contributor

Re: Vlookup and calculation

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

 

Community Support Team
Community Support Team

Re: Vlookup and calculation

Hi @KatkaS ,

 

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


Best Regards
Icey

KatkaS Regular Visitor
Regular Visitor

Re: Vlookup and calculation

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...

 

 

KatkaS Regular Visitor
Regular Visitor

Re: Vlookup and calculation

pic2.jpg

KatkaS Regular Visitor
Regular Visitor

Re: Vlookup and calculation

 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..

KatkaS Regular Visitor
Regular Visitor

Re: Vlookup and calculation

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!

KatkaS Regular Visitor
Regular Visitor

Re: Vlookup and calculation

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!

Community Support Team
Community Support Team

Re: Vlookup and calculation

Hi @KatkaS ,

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

 

Best Regards,

Icey

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)