cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aldelemy
Regular Visitor

New column to calculate profit value

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

 

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @Aldelemy ,

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] )
)

r2.png

 

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

m1.pngm2.pngr1.png

 

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.

View solution in original post

3 REPLIES 3
v-yingjl
Community Support
Community Support

Hi @Aldelemy ,

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] )
)

r2.png

 

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

m1.pngm2.pngr1.png

 

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.

View solution in original post

Thank you so much for this clear answer

sayaliredij
Continued Contributor
Continued Contributor

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.

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors