- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
Sum of to many on different tables
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
08-08-2018 10:41 PM - edited 08-08-2018 10:42 PM
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
Solved! Go to Solution.
Accepted Solutions
Re: Sum of to many on different tables
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
08-09-2018 02:24 AM
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 %]
All Replies
Re: Sum of to many on different tables
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
08-08-2018 11:03 PM
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
Re: Sum of to many on different tables
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
08-08-2018 11:05 PM
well there is a relationship already towards another table with Unique values
both tables refer to the same table and same field
Re: Sum of to many on different tables
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
08-08-2018 11:12 PM
"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
Re: Sum of to many on different tables
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
08-09-2018 02:24 AM
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 %]