How to ignore slicer but keep filter on when creating measure

[jessie_hnd]

Hi, I'm a newbie to Power BI and I'm struggling to create a measure that disregards the slicer but keeps a filter on (where the selected values on the slicer and on the filter are mutually exclusive). I would greatly appreciate some help with this!

My data structure looks like this:

FACTOR ITEM GROUP SCORE
Factor 1 Item 1 Group A 5
Factor 1 Item 2 Group B 1
Factor 1 Item 3 Group C 2
Factor 1 Item 4 Group B 3
Factor 1 Item 5 Group C 4
Factor 2 Item 1 Group A 5
Factor 2 Item 2 Group B 2
Factor 2 Item 3 Group C 3
Factor 2 Item 4 Group B 4
Factor 2 Item 5 Group C 5
Factor 3 Item 1 Group A 5
Factor 3 Item 2 Group B 3
Factor 3 Item 3 Group C 4
Factor 3 Item 4 Group B 5
Factor 3 Item 5 Group C 1

I have a slicer on GROUP and a slicer on FACTOR. Now I want to use Item 1 (Group A) as my reference point and create a measure to calculate the difference in SCORE between other items and Item 1. I would like to be able to drill down to the selected values on both GROUP and FACTOR, and display items with SCORE differences from Item 1 on a chart. 

I created a measure that looks like this:

DIFF = CALCULATE(AVERAGE(SCORE), ITEM = "Item 1") - AVERAGE(SCORE)

There is no issue if I only use the FACTOR slicer; all the SCORE differences are accurately calculated dynamically every time. However, the problem occurs when I use the GROUP slicer and select Group B or Group C. I figured it's because the selected items in the GROUP slicer are mutually exclusive with Group A (Item 1), so the first half of the measure becomes 0 and the SCORE differences are not calculated correctly in such cases. 

My current stop-gap measure is to only use the FACTOR slicer and turn GROUP into an information card or a table for reference purposes. However, it is rather inconvenience as I expect many items to have SCORE differences in reference to Item 1, so being able to analyze them by GROUP would make more logical sense. 

Many thanks in advance!

[amitchandak]

@jessie_hnd , Try like

DIFF = CALCULATE(AVERAGE(Table[SCORE]),filter( all(Table[Item]),  ITEM = "Item 1")) - AVERAGE(Table[SCORE])

or

DIFF = CALCULATE(AVERAGE(Table[SCORE]),filter( all(Table),  ITEM = "Item 1")) - AVERAGE(Table[SCORE])

[jessie_hnd]

Thank you for the quick response! @amitchandak 

I tried both methods; however, I'm not able to achieve the desired result when I select values on both FACTOR and GROUP slicers.

If I use filter( all(Table[Item]), ITEM = "Item 1"), the CALCULATE function returns 0.

If I use filter( all(Table), ITEM = "Item 1"), it appears that the first argument of the FILTER function ignores both the slicer on GROUP as well as the slicer on FACTOR. So my measure takes in the score for Item 1 across all factors rather than the score for Item 1 for a specific factor selected on the FACTOR slicer.

If I use filter( all(Table[Group]), GROUP = "Group A"), the measure is correctly calculated at the GROUP level for a particular FACTOR, taking the average score of all items in the Group. But within the GROUP, the SCORE differences for individual items as compared to Item 1 for each selected FACTOR are not computed correctly at the ITEM level. I'm not sure how to fix the measure to reflect a more granular level of SCORE differences.

What I would like to achieve for this measure is to ignore the slicer on GROUP, keep the slicer on FACTOR, add in a filter for ITEM = "Item 1" or GROUP = "Group A" when computing the measure, and dynamically reflect the SCORE difference at ITEM level for selected GROUP and FACTOR. Both slicers should still work as normal on the dashboard when we need to perform the analysis for each item, group and factor. Is there another way to achieve this?

Many thanks in advance!