cancel
Showing results for
Did you mean:
Member

calculate target vs total sale

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.

1 ACCEPTED SOLUTION

Accepted Solutions
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.

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

2 REPLIES 2
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.

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

Highlighted
Member

Re: calculate target vs total sale

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

Anupam