cancel
Showing results 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

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

 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

1 ACCEPTED SOLUTION
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])`

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.
2 REPLIES 2
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])`

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

Announcements

#### 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.

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors