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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
funkyman50
New Member

Sum of a dim value for each row in a fact table

I have a fact table [Fact].[Surgeries] that has columns like SurgeryDate, SurgeryDuration, SurgeryCost, SurgeonID (key to Dim.Surgeon), SurgeryID (key to Dim.Surgery).

 

In Dim.Surgery, there is a column SurgeryStandardDuration.

 

I am trying to get a comparison of actual duration vs standard duration via two SUM measures. 

 

SUM(Fact.Surgeries[SurgeryDuration]) returns the accurate sum of minutes when grouped by surgeon but SUM(Dim.Surgery[SurgeryStandardDuration]) is only returning a sum of one of each surgery as filtered by the SurgeryID. 

 

Is there a way to write the DAX to apply the SurgeryStandardDuration value to each row in the Surgeries fact before summing or do I need to make a calculated column in Fact.Surgeries and then sum that?

1 ACCEPTED SOLUTION
sjoerdvn
Super User
Super User

Try this measure:

Std Surgery Duration = SUMX('Fact.Surgeries',RELATED('Dim.Surgery'[SurgeryStandardDuration]))

View solution in original post

3 REPLIES 3
sjoerdvn
Super User
Super User

Try this measure:

Std Surgery Duration = SUMX('Fact.Surgeries',RELATED('Dim.Surgery'[SurgeryStandardDuration]))

This worked great. Thank you!

quantumudit
Super User
Super User

Hello @funkyman50 

Could you please provide sample data that fully represents your issue or question in a usable format, rather than a screenshot? Ensure the dataset is anonymized and does not contain any sensitive or unrelated information.

 

Additionally, it would be very helpful if you could share the expected outcome based on the provided data - this can be in any format, including a screenshot. Having a clear reference for comparison will significantly improve the chances of getting the correct solution in the first response.

 

For best practices, you may find the following links useful:

 

Also let us know whether you prefer having a calculated column or, a measure. As far my current understanding of your issue, I think you can explore the RELATED() function in DAX  to resolve your issue.

 

Here are the links to understand how RELATED() function works:

 

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
Visit My Linktree: LinkTree

Proud to be a Super User

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.