Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kg22
New Member

PowerBI - subtract two columns from different tables

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 ClusterProductYearAttributeCost
AX2023Sum10
AX2023Cont.0
AX2024Sum20
AX2024Cont.0
AX2025Sum30
AX2025Cont.1
AY2023Sum10
AY2023Cont.0
AY2024Sum20
AY2024Cont.0
AY2025Sum30
AY2025Cont.1
AZ2023Sum15
  ...  
BH2023Sum20
BH2024Cont.0

 

Table B:

Product ClusterProductYearAttributeCost
AX2023Sum9
AX2023Cont.0
AX2024Sum19
AX2024Cont.0
AX2025Sum35
AX2025Cont.1
AY2023Sum12
AY2023Cont.0
AY2024Sum25
AY2024Cont.0
AY2025Sum20
AY2025Cont.1
AZ2023Sum18
  ...  
BH2023Sum10
BH2024Cont.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

2 REPLIES 2
amitchandak
Super User
Super User

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

@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 ClusterProductYearCost (Table A)Delta
AX2023101
AX2024201

 

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

Product ClusterProductYearCost (Table A)Delta
AX2023101
CL202620-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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors