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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

calculating target per sales group

Hi,

 

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

I have two tables like this:

Table 1

datesalestypetarget
01-01-2018type 1331 149 USD
01-01-2018type 2187 389 USD
02-01-2018type 1331 149 USD
02-01-2018type 2187 389 USD
03-01-2018type 1331 149 USD
03-01-2018type 2187 389 USD
04-01-2018type 1331 149 USD
04-01-2018type 2187 389 USD
05-01-2018type 1331 149 USD
05-01-2018type 2187 389 USD
06-01-2018type 1149 761 USD
06-01-2018type 299 079 USD
07-01-2018type 185 578 USD
07-01-2018type 261 924 USD
08-01-2018type 1331 149 USD
08-01-2018type 2187 389 USD

 

Table 2

dateSellersalestypetarget
01-01-2018person 1type 1149 USD
01-01-2018person 1type 27 389 USD
01-01-2018person 2type 131 149 USD
01-01-2018person 1type 210 000 USD
01-01-2018person 1type 1149 USD
01-01-2018person 2type 27 389 USD
01-01-2018person 1type 131 149 USD
01-01-2018person 3type 210 000 USD
02-01-2018person 1type 1149 USD
02-01-2018person 1type 27 389 USD
02-01-2018person 2type 131 149 USD
02-01-2018person 1type 210 000 USD
02-01-2018person 3type 1149 USD
02-01-2018person 1type 27 389 USD
02-01-2018person 3type 131 149 USD
02-01-2018person 1type 210 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:

datesalestypesoldtargetdifference
01-01-2018type 162 596 USD331 149 USD-268 553 USD
01-01-2018type 234 778 USD187 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

1 ACCEPTED SOLUTION
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

 

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])

calculating_target_per_sales_group

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

 

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])

calculating_target_per_sales_group

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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