Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
Hi @v-ljerr-msft,
Thanks, this is the shortest and fastest way to achieve the desired result.
Anupam
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |