Need help for calculating data

[A_H]

I have the following secnario  


Table 1 (Targets) :

SalesMan	Brand Id	Target to achieve	PeriodCode	PeriodStartDate	PeriodEndDate
555	11	10000	Oct-18	01-10-18	31-10-18
555	12	12000	Oct-18	01-10-18	31-10-18

Table 2 (OrderLines) : 

SalesMan	Brand	Item	SalesAmount	OrderDate
555	11	1000	5000	14-10-18
555	11	1000	1000	15-10-18
555	11	2000	1000	16-10-18
555	12	3000	6000	17-10-18
555	12	4000	1000	18-10-18

I want to add a coulmn to Table 1 as "Actual sales" to calculate the sum of sales amount for each salesman for each brand for each period. 


Thanks in Advance. 

[MFelix · Accepted solution]

Hi @A_H ,

I would create additional tables to make dimension table to relate this two.

SalesMan = SUMMARIZE(ALL(Targets[SalesMan]);Targets[SalesMan])

Brands = SUMMARIZE(ALL(Targets[Brand Id]);Targets[Brand Id])

PeriodCode = SUMMARIZE(ALL(Targets[PeriodCode]);Targets[PeriodCode])

Also would create an additional column on orderlines to have the period code

PeriodCode = FORMAT(OrderLines[OrderDate ];"mmm-yy")

Be aware that this are DAX formulas but this columns and tables can also be created in the query editor.

Also regarding the period you can also create a calendar table to relate with the other tables.

Then make a realtion ship betwen the 3 new tables and the other 2.

Use the columns on the dimension tables for your data and the rest comes from the other tables (as you can see I added a measure with the difference to target):

Check PBI file attach

Regards,

MFelix

[A_H]

Thanks @MFelix  it is worked 🙂