cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jurgenvd Regular Visitor
Regular Visitor

Sum of to many on different tables

hello all

 

I thought it would be a simple calculation but apparantly it's not and i'm breaking my head .

 

Table 1: 

i have the data  Availability in month in percentage 

 

this data is an monthly extract so this aren't unique values,

 

Table 2:

I have percentage  during planned Maintenance,

 

here on this table its possible that we have multiple data per month (multiple planned maintenances)

 

i would like to create a measure

=Table1[Availability %]+Table2[Percentage planned Maintenance]

 

this however is not possible as i have no unique values in both tables i believe, so i will have to write a bigger DAX formule, but here is where i get lost.

 

Thanks for your help

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
jurgenvd Regular Visitor
Regular Visitor

Re: Sum of to many on different tables

Thanks for the answer,

 

I found my solution elsewhere

 

here is what it fixed 

 

Create measure for individual %

 tot %= sum(%per month)

tot planned %= sum(planned maintenance per month).

 

After that create final measure 

[ tot %]  + [tot planned %]

4 REPLIES 4
Highlighted
rajendran Super Contributor
Super Contributor

Re: Sum of to many on different tables

From your message, i understand that the relationship is not set up yet.

 

As you know, Power BI doesnt support Many to Many relationship, which means both common columns has duplicate values in both tables, you cant directly join them . You need to create a relationship table/ bridge table to make relationship.

 

Create a table with Unique values from your joining columns and join both of your tables to this new table and set the relationship.

Now try your calculation.

 

Thanks

Raj

jurgenvd Regular Visitor
Regular Visitor

Re: Sum of to many on different tables

well there is a relationship already towards another table with Unique values

 

both tables refer to the same table and same field

rajendran Super Contributor
Super Contributor

Re: Sum of to many on different tables

"this however is not possible as i have no unique values in both tables i believe" -- i came to that conclusion based on this statement. Do you think you can post some sample data and details about your relationship.

 

Thanks

Raj

jurgenvd Regular Visitor
Regular Visitor

Re: Sum of to many on different tables

Thanks for the answer,

 

I found my solution elsewhere

 

here is what it fixed 

 

Create measure for individual %

 tot %= sum(%per month)

tot planned %= sum(planned maintenance per month).

 

After that create final measure 

[ tot %]  + [tot planned %]

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 375 members 3,827 guests
Please welcome our newest community members: