Subtracting Columns from 2 Tables

[paulfink]

Hi guys,

i need help with DAX as im trying to subtract two columns in two different tables but they each have columns with the same names which i need so that i can have a total.

What i am trying to do is to subtract Column A (Amount) from Column B (Time) but having Column C ( Names) as the overall factor that will total up A and B so that each name in C has a total subtracted amount.

E.g 60 (Amount) - 1.0 (Time ( 1.0 is in 10 different rows)) = 50 for a name in C

Thank you for helping.

[paulfink]

hi guys,

its okay now, no need to help.

it somehow fixed itself.

the A-B did not work it only showed the subtracted on each row so no change but i made a new visual and put in names->subtracted-> amount -> time -> etc. and it worked. So thank you for your help!

[Bizualisation]

Hi Paulfink

That is because of the difference between a column and a measure in PowerBI. The measure you created will be calculated dynamically - so when you added Column C to a visual it will then perform your calculation (A - B) split by column C, although the underlying data doesn't change.

[Bizualisation]

Assuming you have a relationship between the two columns simply make a measure like 

Measure = [Column A] - [Column B]

Now if you build a visualisation, say a table, and put in Column C as your rows and your measure as your value, PowerBI will return the subtracted value for each item in Column C

Does that answer your question? 

[amitchandak]

First Aggregate then you be able to

Like Sum(table1[A]) - Sum(Table2[A])

But the information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[paulfink]

hi amitchandak,

here is a dummy Excel file i put together to show my situation

on the left is an example of the data i have, the right is what is should look like in power bi.

This is probably a simple A - B formula but on my power bi it is subtracting it per row instead of totally the subtracted amount.

thank you.