Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
davidi4524
Helper III
Helper III

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
v-ljerr-msft
Employee
Employee

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

View solution in original post

2 REPLIES 2
v-ljerr-msft
Employee
Employee

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

Hi @v-ljerr-msft,

 

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

 

 

Anupam

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.