calculate target vs total sale

[davidi4524]

hi, i need to calculate target vs total sale per date

i have two tables:

1. target

columns: 

date	branch	target	key
6/19/2017	4524	100	6.19.20174524
6/20/2017	4524	200	6.20.20174524
6/21/2017	4524	300	6.21.20174524
6/22/2017	4524	400	6.22.20174524
07/01/2017	4524	1000	7.1.20174524

2.sales

columns: 

date	branch	sale	key
6/19/2017	4524	10	6.19.20174524
6/19/2017	4524	20	6.19.20174524
6/19/2017	4524	30	6.19.20174524
6/19/2017	4524	40	6.19.20174524
6/19/2017	4524	50	6.19.20174524
6/20/2017	4524	30	6.20.20174524
6/20/2017	4524	40	6.20.20174524
6/20/2017	4524	50	6.20.20174524
6/20/2017	4524	60	6.20.20174524
6/20/2017	4524	70	6.20.20174524
6/20/2017	4524	80	6.20.20174524
6/20/2017	4524	90	6.20.20174524
6/21/2017	4524	1	6.21.20174524
6/21/2017	4524	2	6.21.20174524
6/21/2017	4524	3	6.21.20174524
6/21/2017	4524	4	6.21.20174524
6/21/2017	4524	5	6.21.20174524
6/21/2017	4524	6	6.21.20174524
6/22/2017	4524	20	6.22.20174524
6/22/2017	4524	40	6.22.20174524
6/22/2017	4524	60	6.22.20174524
6/22/2017	4524	80	6.22.20174524
6/22/2017	4524	100	6.22.20174524

i need to show on power bi :

date	branch	target	key	total sale	delta
6/19/2017	4524	100	6.19.20174524	150	50
6/20/2017	4524	200	6.20.20174524	420	220
6/21/2017	4524	300	6.21.20174524	21	-279
6/22/2017	4524	400	6.22.20174524	300	-100
07/01/2017	4524	1000	7.1.20174524	null	0

how can i do it? i tried to do in the matrix visual with no succes, can someone give me syntax to create relevant calculation?

also' its very importent to show the target even if in there is no value in the sales table per target via key.

thank you.

[v-ljerr-msft · Accepted solution]

Hi @davidi4524,

First, create a relationship between the 'target' table and the 'sales' table with the 'key' column if there isn't yet.

Then you should be able to use the formulas below to add new calculate columns in the 'target' table to get your expected result. 

total sale = CALCULATE(SUM(sales[sale]),RELATEDTABLE(sales))

delta = IF ( ISBLANK ( target[total sale] ), 0, target[total sale] - target[target] )

Regards

[anupampandey]

Hi @v-ljerr-msft,

Thanks, this is the shortest and fastest way to achieve the desired result.

Anupam