New column to calculate profit value

[Anonymous]

Hi, Is it possible to add a new column that calculates the value using the relationships between the 3 tables?

Actually, I want to calculate the total profit of each country, of a real estate company that have branches in many countries and each branch, has many properties

Table 1: Country Id, Country Name

Table 2: Country Id, Property Id

Table3: Property Id, Profit

The difficulty is that there are several properties in each country, which means several values represent the profits in each country. so the query should sum all these profit values for each country.

you can see the actual tables in this link: https://prntscr.com/118mfhb

[v-yingjl · Accepted solution]

Hi @Anonymous ,

To show the result clearly, have modified your sample data. Based on your description, DAX and power query both could achieve this.

1. You can use DAX formula to create a calculated column directly:

TotalProfit =
CALCULATE (
    SUM ( Profit[TotalProfit] ),
    'Country'[CountryID] IN DISTINCT ( 'Property'[CountryID] ),
    'Property'[PropertyID] IN DISTINCT ( 'Profit'[PropertyID] )
)

2. You can also use the 'Merge' feature in power query to merge table twice to get the same result:

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much for this clear answer

[sayaliredij]

I think this problem is easily addressed if you just import this table in power bi and make the relationship between them.

You can create a measure 

Profit = SUM(Profit)

In this way, you can have profit available on each level . You just need to drag and drop columns

In case you want to create new table out of it

CountryWise = SUMMERIZECOLUMNS( CountryName, "Profit", SUM(Profit))

Regards,

Sayali 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.