PowerBI - subtract two columns from different tables

[kg22]

Hi,

I need your help with the following problem. I have two different but similar tables from which I want to subtract two columns (Cost). For confidentiality reasons, I cannot provide the data but I will give a simple example:

Table A (imagine that I have multiple Product Clusters, Products, Years, etc. - this is a simplified view):

Product Cluster	Product	Year	Attribute	Cost
A	X	2023	Sum	10
A	X	2023	Cont.	0
A	X	2024	Sum	20
A	X	2024	Cont.	0
A	X	2025	Sum	30
A	X	2025	Cont.	1
A	Y	2023	Sum	10
A	Y	2023	Cont.	0
A	Y	2024	Sum	20
A	Y	2024	Cont.	0
A	Y	2025	Sum	30
A	Y	2025	Cont.	1
A	Z	2023	Sum	15
		...
B	H	2023	Sum	20
B	H	2024	Cont.	0

Table B:

Product Cluster	Product	Year	Attribute	Cost
A	X	2023	Sum	9
A	X	2023	Cont.	0
A	X	2024	Sum	19
A	X	2024	Cont.	0
A	X	2025	Sum	35
A	X	2025	Cont.	1
A	Y	2023	Sum	12
A	Y	2023	Cont.	0
A	Y	2024	Sum	25
A	Y	2024	Cont.	0
A	Y	2025	Sum	20
A	Y	2025	Cont.	1
A	Z	2023	Sum	18
		...
B	H	2023	Sum	10
B	H	2024	Cont.	0

Both come from the same Excel file and tab, with the only difference being the file version (version controlled on SharePoint). Many users can edit this file and I need to provide a quarterly review and capture the Delta.

Note that while transforming my data, I am filtering the data creating a new column to split the Sum from Cont. entries (Add Conditional Column filtering Sum).

What I have done is add an Index column to each table, create a 1-1 relationship and it worked. However, when I took another file version (same structure, same items on the same lines) it messed up the Indexing resulting in a mess.

I understand that the Indexing idea was not perfect as it cannot handle changes in line items - which can happen.

Is there anything else I can try?

Thanks,

George

[amitchandak]

@kg22 , you need to create common dimensions product, cluster product, attribute, year etc and join with both tables and then use them in visual

Power BI- Power Query: When I asked you to create common tables: https://youtu.be/PqfGW6pl1Sw

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...

[kg22]

@amitchandak 

Thank you for the information. I've created the proposed structure and managed to get the required visuals i.e. the Delta of all costs between the two different tables.

This is done using the following Measure:

   Delta (K€) = SUM('Table A'[Cost]) - SUM('Table B'[Cost]).

However, I have added a Table below this visual to present the entries contributing to this result i.e. those having a Delta not equal to "0". While filtering out all such entries, for some reason, I am seeing most of the entries reported with their negative value.

To give an example, if a 2023 item has 10 as Cost in Table A and it is the same in Table B, I would expect the result (Delta) to be 0. However, the outcome is -10.

This is a sample of the expected result:

Product Cluster	Product	Year	Cost (Table A)	Delta
A	X	2023	10	1
A	X	2024	20	1

And what I see (the second shouldn't be appearing assuming the cost is the same in both tables):

Product Cluster	Product	Year	Cost (Table A)	Delta
A	X	2023	10	1
C	L	2026	20	-20

However, the calculation should be ok as the visuals agree with the math (cross-checked in Excel).

Any idea why this is happening?

Thank you!

George