Forum Discussion
Adjust targets when slicing/filtering
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
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.
5 Replies
- amitchandak
Super User
This you should able to do in Matrix visual.
Can you share sample data and sample output. Mark me @
- amitchandak
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.
v-yuta-msftCommunity Support
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