Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
GUSers
Regular Visitor

SUMX across three Tables

I'm struggling to figure out how to apply some calculations across three tables. I've been able to get SUMX to work with two of these tables with direct relationship, but now I've added a third table that doesn't connect to the other table that I need to multiply values with. I have laid out a diagram of what I've got going on below.

  1. I have an Events table with Quantities being made and dates (even though that isn't represented in the diagram). This ties to what is being made by the MasterID column relating to the ID column from the Work table.
  2. I have a Work table that has the Part Number and its Sale Price.
  3. I have my newest table called, Cost, which has Part Numbers and a Manufacturing Cost.

What I'd love to do is use SUMX to take 'Events'[Qty] * 'Cost'[MfgCost], but that's not working due to the lack of direct relationship. I've been trying to figure a way to populate the Work table with a corresponding Manufacturing Cost value based on the Part Number relationship, but have also been striking out. My assumption is that if I can do that, then I could use SUMX to take the 'Events'[Qty] * the 'Work'[newMfgCost].

 

From there I'm looking to calculate some Margins (both in dollars and percentage), which should be easy to get once I have that Extended Manufacturing Cost.

 

GUSers_0-1686777857362.png

Hoping someone can help with some guidance here.
I'm in a Direct Query setup with all the data as there are real-time changes being incorporated with my dataset.

1 ACCEPTED SOLUTION

Thanks for the response. For whatever reason I continue to get the following error/warning message:

  • The column 'Cost[MfgCost]' either doesn't exist or doesn't have a relationship to any table available in the current context.

I don't understand because the relationship appears in the Model just as I'd included in the screen shot above.

  • Many (Work) to One (Cost)

I found some stuff online regarding doing Joins. So I joined tables and created a new table with the data that I was looking for. It seems a little clunky, but easier than fiddling with formulas that I was striking out on for awhile.

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@GUSers , Create a work a new column work table 

 

Cost 1 = related ('Cost'[MfgCost])

 

then create a new column in the Events

 

Cost 2 = related(Work[Cost 1])

Thanks for the response. For whatever reason I continue to get the following error/warning message:

  • The column 'Cost[MfgCost]' either doesn't exist or doesn't have a relationship to any table available in the current context.

I don't understand because the relationship appears in the Model just as I'd included in the screen shot above.

  • Many (Work) to One (Cost)

I found some stuff online regarding doing Joins. So I joined tables and created a new table with the data that I was looking for. It seems a little clunky, but easier than fiddling with formulas that I was striking out on for awhile.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors