Sum related when there is a one to many relationship

[ny326n]

Hi!

I need some help on this one:

I have two tables related trough dates

Table 1 contains dates as unique value:

1 jan 2017

2 jan 2017

3 jan 2017

Table 2 contains one date column and one order value column:

1 jan 2017          345

1 jan 2017          435

2 jan 2017          534

2 jan 2017          354
etc. 

What i want to do is to insert the sum of each dates order value from table 2 into table 1. How do I solve this?

Thank you in advance! 

[Datatouille]

Hi,

First, make sure you have an active relationship between your 'Dates' Table and your 'Order' Table (View Relationships).

Second, create the following measure: Total Amount = Sum(OrderTable[OrderValue])

Third, create a graph which uses the date from the Dates table as X-axis and the measure "Total Amount" in the "values" field.

[ny326n]

Thank you for your reply, however, this is not exacly what I want. I want the order value to sum accordning to the dates. So all rows with 1 jan 2017 in Table 2 sums up on one row, 1 jan 2017 in table 1. By only writing sum it simply sums up the total order value and copy it for each row. 

Was this clear? Thanks!

[Datatouille · Accepted solution]

You want it to be loaded in the Table 1 ?

In that case, you need to create a calculated column in Table 1 :

Amt = Calculate(Sum(OrderTable[OrderColumn]) )

[JonMKDD]

I kept trying group by to get to a similar solution, thanks!

[ny326n]

Ah, so easy! Thank you very much! 🙂