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-yuezhe-msft]

@davidi4524,

Firstly, in Query Editor of Power BI Desktop, click on target table, then choose “Merge Queries-> Merge Queries as New”.

Secondly, expand the sale column from the NewColumn as shown in the following screenshot.

Thirdly, create the measures below in the merged table.

target value = MAX(Merge1[target])
total sale = SUM(Merge1[NewColumn.sale])
delta = IF([total sale]=BLANK(),0,[total sale]-[target value])

At last, create a table visual as follows. Make sure you select “Show items with no data” for date field.

Regards,

[davidi4524]

hi,i forgot somthing, in the sales table i have a column named "type"

i need to show only data with the type 1 or 2 .

the type column didnt show on the merge table.

how can i filter only data with specific type? thanks.

[v-yuezhe-msft]

@davidi4524,

In second step, you can expand the sale and type columns from the NewColumn, then you are able to filter the NewColumn.type column to only contain type 1 and type 2 data.

Regards,

[davidi4524]

ok, and if i whant to show type 1 or 2 and its ok

but if i have a target with no sales its hide it when i applied this filter

what i neeed to do?

[v-yuezhe-msft]

@davidi4524,

Could you please post the complete sample data of your sales table and post the expected result here?

Regards,

[davidi4524]

sales table:

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

target table:

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

need to show:

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

note, that for the target in 7/1/2017 there is no sales data , but i steel need to show him.

thanks.

[anupampandey]

Hi,

Based on your sample data I have done it in a simple way.
I have created a relationship between target & sales (on field name "key") table than create 3 measures as below:

Measure1 (Target table):: Total_Target = sum(target)
Measure2 (Sales table):: Total_Sales = sum(sales)
Measure3 (Sales table):: Delta = Total_Target - Total_Sales

To filter the "type of document" I have put the slicer.

Hope this will address your concern.

All the best!

Regards,
Anupam

[v-yuezhe-msft · Accepted solution]

@davidi4524 ,

Based on your sample data, the type column doesn't take effect on your expected result, you can add a type slicer to filter the table visual.

Besides, please check the last screenshot in my first reply. 

'At last, create a table visual as follows. Make sure you select “Show items with no data” for date field.'

Regards,

[davidi4524]

hi, thank you very much for this detaild answer. im gonna try and let you know.