Adjust targets when slicing/filtering

[Anonymous]

Hello,

I have a orders table, with info about that order. I also have a Target table, which gives detailed information about the targets.

Now I want to create a visual with order period on X-axis, and count of orders and target on the Y-axis 

Order table

Order nr	Year	SalesGroup	Channel
1	Jan 2019	Direct Sales	B2B
2	Feb 2020	Indirect Sales	B2C
3	Jan 2019	Indirect Sales	B2C
4	Jan 2019	Direct Sales	B2B
5	Feb 2020	Direct Sales	B2C
6	Jan 2019	Direct Sales	B2B
7	Feb 2019	Indirect Sales	B2C
8	Jan 2019	Indirect Sales	B2B
9	Jan 2020	Indirect Sales	B2C
10	Jan 2020	Direct Sales	B2B
11	Jan 2020	Indirect Sales	B2C
12	Feb 2019	Indirect Sales	B2C
13	Feb 2019	Indirect Sales	B2C
14	Feb 2020	Indirect Sales	B2C
15	Jan 2019	Direct Sales	B2B
16	Feb 2019	Indirect Sales	B2C
17	Feb 2020	Indirect Sales	B2B

Target:

Important is that Target B2B + Target B2C add up to Target total. We also want to see a total of Sales Group

SalesGroup	Date	Target (total)	Target B2B	Target B2C
Direct Sales	Jan 2019	4	3	1
Direct Sales	Feb 2019	6	2	4
Indirect Sales	Jan 2019	8	3	5
Indirect Sales	Feb 2019	5	2	3
Direct Sales	Jan 2020	5	5	-
Direct Sales	Feb 2020	6	4	2
Indirect Sales	Jan 2020	10	6	4
Indirect Sales	Feb 2020	12	3	9

Desired output:

A visual which gives me the right amount of orders and target, even if I slice or filter.

So, with no filters I want to see total orders en total target:

jan 19: 6 orders, target 12

feb 19: 4 orders, target 11

jan 20: 3 orders, target 15

feb 20: 4 orders, target 18

When I filter on Indirect sales:

jan 19: 2 orders, target 8

feb 19: 4 orders, target 5

jan 20: 2 orders, target 10

feb 20: 3 orders, target 12

When I filter on Indirect sales and B2C:

jan 19: 1 orders, target 5

feb 19: 4 orders, target 3

jan 20: 2 orders, target 4

feb 20: 2 orders, target 9

[v-yuta-msft]

@Anonymous ,

Desired output:

A visual which gives me the right amount of orders and target, even if I slice or filter.

So, with no filters I want to see total orders en total target:

jan 19: 6 orders, target 12

feb 19: 4 orders, target 11

jan 20: 3 orders, target 15

feb 20: 4 orders, target 18

When I filter on Indirect sales:

jan 19: 2 orders, target 8

feb 19: 4 orders, target 5

jan 20: 2 orders, target 10

feb 20: 3 orders, target 12

When I filter on Indirect sales and B2C:

jan 19: 1 orders, target 5

feb 19: 4 orders, target 3

jan 20: 2 orders, target 4

feb 20: 2 orders, target 9

---

Could you clarify more details and the calculation logic of the result?

Regards,

Jimmy Tao

[amitchandak]

This you should able to do in Matrix visual.

Can you share sample data and sample output. Mark me @

[Anonymous]

Hi @amitchandak and other users 🙂

Here is the data set

https://1drv.ms/u/s!AvC8sXOgGhuRqXFNeFSjOVdsC7C2?e=yETtbX

[amitchandak · Accepted solution]

@Anonymous , I corrected the data model. File is attcahed post signature

Also refer,https://docs.microsoft.com/en-us/power-bi/guidance/

Appreciate your Kudos.

[amitchandak]

@Anonymous Does the above reply helps. if you need more help make me @

Appreciate your Kudos.