Forum Discussion
Selecting data excluded from slicer filter
Each one of our projects and staff members are allocated to a (single) cost centre (say 01, 02 and 03) and region (A, B and C). Any person can book time to any project, irrespective of region or cost centre. I have the time entry data for the projects, something like:
| Project cost center | Project region | Employee cost center | Employee region | Cost |
| 01 | B | 03 | C | 218 |
| 01 | B | 03 | A | 184 |
| 01 | B | 01 | C | 73 |
| 01 | B | 01 | C | 671 |
| 02 | A | 01 | A | 772 |
| 02 | A | 01 | C | 587 |
| 03 | B | 03 | A | 645 |
| 01 | B | 01 | A | 11 |
| 01 | C | 01 | A | 865 |
| 03 | C | 03 | C | 690 |
| 03 | A | 01 | A | 200 |
| 02 | C | 03 | C | 801 |
| 01 | C | 01 | C | 281 |
| 03 | C | 01 | B | 436 |
The user will select one or more cost centers and one or more regions. I then want to show the sum of cost (split by cost centre) from people outside the selected cost center/region combination booked to the projects in the selected cost center/region combination. The next step would be to show the sum of cost from people in the selected cost center/region combination booked to projects outside the selected cost center/region combination.
My first idea was to somehow identify the records not selected in the sliders by creating automatic hidden slicers but I can't find a way to do this. Then I tried to identify (for each row) whether that row should be included in the analysis or not (some IF formula) but again get stuck.
I'd appreciate it if anyone can point me in the right direction.
2 Replies
- amitchandak
Super User
Musashi01 , Create an independent Table with Employee cost center
cost center = distinct(Table[Employee cost center])
then create a measure like
Measure =
var _tab = summarize(Filter(Table, Table[Employee cost center] in allselected('cost center'[Employee cost center])), Table[Project region])
return
calculate(sum(Table[Cost]), filter( Table, Table[Project region] in _tab) )
I get an error "The syntax for 'RETURN' is incorrect."
I also suspect that using "in allselected" will include the selected office, not exclude it.
