Relationsip wrong direction

[Anonymous]

Hi guys,

i have a relation between two tables (highlighted in orange):

At the moment the direction goes from the small table (one side) to the big table (many side). 

What i want to do is to sum up values on the many side and display them on the one side. So i would assume the relation should be exactly the other way around. But when i switch the direction to: many-to-one it won't change the direction.

So basically my question is how do sum up the values on the many side and write them in the table on the one side?

Do i need to switch the direction of the relation for that? 

Thx guys

[AllisonKennedy]

For summing the values, no need to switch direction. You can set direction to both or single, but when single you can't change the direction, it always points to the many side. 

@Anonymous  I have updated this post with more accurate info and explanation: 

The issue is with the DAX Context. Calculated columns will calculate the total sum for each row, but a calculated MEASURE will look at the filter context of the report view, so just create a new calculated MEASURE:

Total= SUM(manytable[valuetosum])

Then add the column from the 1 side table to a matrix or table visualization rows, and add the Total MEASURE to the values of the matrix.

That's it, the relationship will do the trick. 

You can also use a visualization to achieve this result without needing to actually create the DAX calculation, just put the many value in VALUES and the one side table in rows. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

@AllisonKennedyThanks for the quick reply! When i do the sum in the one side table it will create a sum but it always creates the same sum for each article: 

One side table:

What should be summed up only should be all the sales for one article in one year (underlined in red) coming from the many side. 

[AllisonKennedy · Accepted solution]

Yes, you're right, sorry, that was a rookie mistake, I know way better than that - late night brain. As a general rule of thumb, never use a SUM or aggregate measure in a calculated column. Put it in a MEASURE and in the visual instead, or try:

Net order qtd. = CALCULATE(SUM('Proift Contribution'[Net order qtd.])) but MEASURE is preferred. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

Perfect it works!