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 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?

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.

3 Replies

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