Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Power BI Summing Issue. Many to one Relationship

I have two tables with Many to One relationship.

Alumni Affiliations Table, A single ID_NUMBER can have more than 1 Affliated schools (Many)

ID_NUMBER	AFFILATION_SCHOOL
0029000017	Undergraduate College
0029000017	Graduate School
0029000047	Athletics
0029000047	Undergraduate College
0034000003	Engineering
0041000086	Education Affiliate
0043000076	Dentistry
0046000004	Law
0047000061	Athletics
0047000061	Engineering
0049000000	Athletics
0049000000	Law

Metrics Table, ID_Number is unique, it shows total money raised for each student ID.(One)

ID_NUMBER	Total Money Raised
0029000017	1210
0029000047	10
0034000003	670
0041000086	0
0043000076	100
0046000004	0
0047000061	0
0049000000	0
0051000003	50

Currently there is a Many to One realtionship with cross filter direction set to Both:

Data Model

When I do a table that shows Total money raised by Affiliation School the Sum is not correct,

If you add the values manually, the toal is 3210.00

Why are these values wrong and what can I do to correct the Total values by school?

Thanks for the help!

Jay

Tips and Tricks

v-deddai1-msft
6 years ago
Hi jayPBI23,

Would you please use the following measure?

sum = SUMX('Alumni Affiliations Table', RELATED('Metrics Table'[Total Money Raised]))

When you use sum to calculate total, this formula lacks a list filter. When you use sumx, it is equivalent to an iterator. It traverses the Alumni Affiliations Table and sums all the values.

And you can refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ETMy0KXSYp9Cldw6cCowRIIBxE4pMGATEgoLWLYlTInyow?e=h8JTcf

Best Regards,
Dedmon Dai

6 Replies

amitchandak
Super User
6 years ago
Can allocate the value like

divide(sum(Table2[Total Money Raised]), count(Table1[ID_NUMBER]))
- amitchandak
  Super User
  6 years ago
  I think your initial calculation is correct. Because your total should not count repeated amount ??
piyushszope
Helper I
6 years ago
Hi,

I think there is an error in the data model and the relationship. Please verify the data again and upload the pbix file. Because as I can see in the above Alumni Affiliations Table, the Id number is allocated to multiple AFFILATION_SCHOOL.
- Anonymous
  Not applicable
  6 years ago
  Hi,
  
  Yes one student ID can be affiliated with more than one school. This is why there is a many to one relationship.
  - piyushszope
    Helper I
    6 years ago
    Then to accomplish that you can create a related calculated column or measure.
    
    And then you can get your desired solution.
    
    If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution.
    Thanks!