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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.