Forum Discussion

akrupa's avatar
akrupa
Regular Visitor
6 years ago
Solved

Data modeling

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 fro...
Need Help
Tips and Tricks
Tutorial Requests
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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/EdHeFvOa845Goc82mDtDdqcBouerGA9esyRwX3TcKV-Bzw

     

    Hope this would help.

v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

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/EdHeFvOa845Goc82mDtDdqcBouerGA9esyRwX3TcKV-Bzw

 

Hope this would help.

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      6 years ago

      Hi akrupa ,

       

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

      Many thanks.

       

       

      Best Regards,

      Kelly