cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davidi4524 Member
Member

calculate target vs total sale

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

i have two tables:

1. target

columns: 

datebranchtargetkey
6/19/201745241006.19.20174524
6/20/201745242006.20.20174524
6/21/201745243006.21.20174524
6/22/201745244006.22.20174524
07/01/2017452410007.1.20174524

 

 

2.sales

columns: 

datebranchsalekey
6/19/20174524106.19.20174524
6/19/20174524206.19.20174524
6/19/20174524306.19.20174524
6/19/20174524406.19.20174524
6/19/20174524506.19.20174524
6/20/20174524306.20.20174524
6/20/20174524406.20.20174524
6/20/20174524506.20.20174524
6/20/20174524606.20.20174524
6/20/20174524706.20.20174524
6/20/20174524806.20.20174524
6/20/20174524906.20.20174524
6/21/2017452416.21.20174524
6/21/2017452426.21.20174524
6/21/2017452436.21.20174524
6/21/2017452446.21.20174524
6/21/2017452456.21.20174524
6/21/2017452466.21.20174524
6/22/20174524206.22.20174524
6/22/20174524406.22.20174524
6/22/20174524606.22.20174524
6/22/20174524806.22.20174524
6/22/201745241006.22.20174524

i need to show on power bi :

 

datebranchtargetkeytotal saledelta
6/19/201745241006.19.2017452415050
6/20/201745242006.20.20174524420220
6/21/201745243006.21.2017452421-279
6/22/201745244006.22.20174524300-100
07/01/2017452410007.1.20174524null0

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
v-ljerr-msft Super Contributor
Super Contributor

Re: calculate target vs total sale

Hi @davidi4524,

 

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

 

relationship1.PNG

 

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

total sale = CALCULATE(SUM(sales[sale]),RELATEDTABLE(sales))
delta = IF ( ISBLANK ( target[total sale] ), 0, target[total sale] - target[target] )

c1.PNG

 

Regards

2 REPLIES 2
Highlighted
v-ljerr-msft Super Contributor
Super Contributor

Re: calculate target vs total sale

Hi @davidi4524,

 

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

 

relationship1.PNG

 

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

total sale = CALCULATE(SUM(sales[sale]),RELATEDTABLE(sales))
delta = IF ( ISBLANK ( target[total sale] ), 0, target[total sale] - target[target] )

c1.PNG

 

Regards

Re: calculate target vs total sale

Hi @v-ljerr-msft,

 

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

 

 

Anupam