• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Need strong query to calculate profit value |Power Query|Excel Forum|My Online Training Hub

You are here: Home / Need strong query to calculate profit value |Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryNeed strong query to calculate prof…
sp_PrintTopic sp_TopicIcon
Need strong query to calculate profit value
Avatar
Ahmad AlDelemy
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 6, 2021
sp_UserOfflineSmall Offline
1
April 11, 2021 - 5:21 am
sp_Permalink sp_Print sp_EditHistory

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.

new-value.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage new-value.png (172 KB)
sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4614
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
April 11, 2021 - 10:59 am
sp_Permalink sp_Print

Hi Ahmed,

As answered to the same question you posted on YouTube:

If you use the Country Name from Table 1 and the Profit from Table 3 in a PivotTable or chart/visual, you should get the result you want, assuming the relationships are set up correctly.
 
Mynda
 
 
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 880
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
3
April 11, 2021 - 6:07 pm
sp_Permalink sp_Print

Hello,

Or you can simplify this and have one table with following structure: Country Name, Property, Profit

Br,
Anders

Avatar
Ahmad AlDelemy
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 6, 2021
sp_UserOfflineSmall Offline
4
April 12, 2021 - 12:16 am
sp_Permalink sp_Print

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    

Avatar
Ahmad AlDelemy
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 6, 2021
sp_UserOfflineSmall Offline
5
April 12, 2021 - 4:22 am
sp_Permalink sp_Print sp_EditHistory

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.

[Image Can Not Be Found]
 
 
Fast translate
 
Icon translate
 
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 880
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
6
April 12, 2021 - 2:45 pm
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4614
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
April 12, 2021 - 6:01 pm
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Ahmad AlDelemy
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 6, 2021
sp_UserOfflineSmall Offline
8
April 15, 2021 - 10:49 am
sp_Permalink sp_Print sp_EditHistory

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?

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Tracy English
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
jaryszek: 183
Newest Members:
Jennifer Rodriguez-Avila
Khaled Ibrahim
Kiran Supekar
Lisa Myers
Ronald White
Ginette Guevremont
Taryn Ambrosi
Mark Davenport
Christy Nichols
Harald Endres
Forum Stats:
Groups: 3
Forums: 24
Topics: 6530
Posts: 28602

 

Member Stats:
Guest Posters: 49
Members: 32820
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.