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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ricardomadaleno
Frequent Visitor

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
Employee
Employee

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

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
Employee
Employee

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

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.

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.