Data modeling

[akrupa]

Hello!

I wold like to make a subtraction two columns in a different tables. This columns contians amount, how can I do this in Power BI?? I can't do this in my data source because it is directly from Business Central. I created a relationship between two tables but not using columns that contain amount because this is not possible.  Is there any solution??? 

Moreover, I must create a table report, will Power BI handle it?

[v-kelly-msft · Accepted solution]

Hi @akrupa ,

According to your request, I have created 2 tables, which are shown as below:

And I create a relationship between the 2 tables using column “id” as primary key:

Then you have 2 ways to make a subtraction between the two columns:

1.create a measure:

  measure = CALCULATE(SUM(Sheet2[Amount]))-CALCULATE(SUM(Sheet3[Amount2]))

2.create a calculated column:

   subtraction = 'Sheet2'[Amount]-CALCULATE(SUM(Sheet3[Amount2]))

ALL  the above 2 ways will finally show the same as below when you put them in a table visual:

As for the related .pbix file you can turn to the URL:

 https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/EdHeFvOa845Goc82mDtDd...

Hope this would help.

[akrupa]

Thank you @v-kelly-msft  ! 

[v-kelly-msft]

Hi @akrupa ,

It's pleasant that your problem has been solved, could you please mark the reply as Answered?

Many thanks.

Best Regards,

Kelly