Log In
Register
Home
April 6, 2021
Offline
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 for each country.
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.
See Answer
July 16, 2010
Offline
Hi Ahmed,
As answered to the same question you posted on YouTube:
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Or you can simplify this and have one table with following structure: Country Name, Property, Profit
Br,
Anders
April 6, 2021
Offline
Thanks for your reply, I could find the total profit through Power Bi and also through PivotTable. However, I am trying to use a formula to find that like using Vlookup or index without using PivotTable. just trying to practice use complex tools.
Thanks again
April 6, 2021
Offline
Thank you, Anders, for your response, I think the data is complex and overlapping, and it's not that easy to do the table as you mentioned. As you can see from the attached worksheets, the company has several agents in several countries, and each agent has several properties. And for each property, many values represent the profits for several years.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Yes, the data also seems to be part of a training excercise.
You can join data using relationships and Pivot, as Mynda writes in her reply, but as this post is in the Power Query section I assume you want a PQ solution to tidy up the data and I would go for a simplified table so I could use a Pivot Table without needing to create relationships.
Br,
Anders
July 16, 2010
Offline
Hi Ahamd,
There's no formula based solution for this because formulas cannot detect relationships that are indirect, i.e. route via multiple tables. That's why you must use Power Pivot so you can create relationships.
Mynda
Answers Post
April 6, 2021
Offline
Thank you for your response Anders and Mynda, I agree with you.
I have created relationships between tables and have been able to get required using the following DAX formula:
TotalProfit =
CALCULATE (
SUM ( Profit[TotalProfit] ),
'Country'[CountryID] IN DISTINCT ( 'Property'[CountryID] ),
'Property'[PropertyID] IN DISTINCT ( 'Profit'[PropertyID] )
)
By the way, I have an interesting question and worth thinking about the same tables but using Power BI, but I do not know where the best place to post it?
1 Guest(s)
