calculating target per sales group

[ricardomadaleno]

Hi,

I'm new to power bi and to DAX, and I'm struggling with this.

I have two tables like this:

Table 1

date	salestype	target
01-01-2018	type 1	331 149 USD
01-01-2018	type 2	187 389 USD
02-01-2018	type 1	331 149 USD
02-01-2018	type 2	187 389 USD
03-01-2018	type 1	331 149 USD
03-01-2018	type 2	187 389 USD
04-01-2018	type 1	331 149 USD
04-01-2018	type 2	187 389 USD
05-01-2018	type 1	331 149 USD
05-01-2018	type 2	187 389 USD
06-01-2018	type 1	149 761 USD
06-01-2018	type 2	99 079 USD
07-01-2018	type 1	85 578 USD
07-01-2018	type 2	61 924 USD
08-01-2018	type 1	331 149 USD
08-01-2018	type 2	187 389 USD

Table 2

date	Seller	salestype	target
01-01-2018	person 1	type 1	149 USD
01-01-2018	person 1	type 2	7 389 USD
01-01-2018	person 2	type 1	31 149 USD
01-01-2018	person 1	type 2	10 000 USD
01-01-2018	person 1	type 1	149 USD
01-01-2018	person 2	type 2	7 389 USD
01-01-2018	person 1	type 1	31 149 USD
01-01-2018	person 3	type 2	10 000 USD
02-01-2018	person 1	type 1	149 USD
02-01-2018	person 1	type 2	7 389 USD
02-01-2018	person 2	type 1	31 149 USD
02-01-2018	person 1	type 2	10 000 USD
02-01-2018	person 3	type 1	149 USD
02-01-2018	person 1	type 2	7 389 USD
02-01-2018	person 3	type 1	31 149 USD
02-01-2018	person 1	type 2	10 000 USD

And I'm trying to build a measure that will allow me to know the target per sales type per day, so i can build something like this on a tbale visual:

date	salestype	sold	target	difference
01-01-2018	type 1	62 596 USD	331 149 USD	-268 553 USD
01-01-2018	type 2	34 778 USD	187 389 USD	-152 611 USD

I've tried this:

CALCULATE(SUM('Table 1'[Target]); filter('Table 1'; 'Table 1'[date]=[Current_Date] && 'Table 1'[salestype]='Table 2'[salestype]))

but no matter what i try it always show the sum of both types target or it comes back saying that a single value could not be found in the salestype column.....

Can anyone help?

Thanks

[v-jiascu-msft · Accepted solution]

Hi @ricardomadaleno,

Please check out the demo in the attachment. You need to adjust the data model. 

1. Create a date table. 

2. Create a SalesType table. 

3. Establish relationships.

4. Create a measure.

Difference = sum(Table2[target]) - sum(Table1[target])

Best Regards,

Dale

[ricardomadaleno]

Thank you so much for your help! it worked briliantly...