Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Hi @KatkaS ,
Please create the columns in Data View or Report View, not Power Query Editor. These are DAX expressions.
Best Regards,
Icey
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!
Hi @KatkaS ,
Please create the columns in Data View or Report View, not Power Query Editor. These are DAX expressions.
Best Regards,
Icey
Thank you so much for your help, Icey!!!!!
It worked for me and the links you sent last time are very useful too!!!
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.
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..
Hello Icey,
is it ok if I email it to you?
Thank you.
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...
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
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!
Hi.
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
100 | |
69 | |
68 | |
44 |
User | Count |
---|---|
145 | |
106 | |
105 | |
90 | |
65 |