Ignore Slicer selections for one bar in stacked bar chart

[chinni611]

Hi , 

I am trying to ignore a slicer selections for one bar in a graph which shows the national average. 

I created a table and measure and created relationship by which i am able to get the avg into the field . 

Here is the dax measure below which is not working . Please help 

I need the avg to be shown in the X axis along with territory , It is showing currently , when i select area or region it disappears

 Avg Terr = VAR _selected = SELECTEDVALUE(Territory[TERR_NAME])

VAR _result = IF(

    _selected = "Avg.",CALCULATE(AVERAGEX(SUMMARIZE('Lookup_Table','Lookup_Table'[Use Case],"toAverage",[Suggestions_SalesAlert]),[Suggestions_SalesAlert]), ALLEXCEPT('terr_hier_ csv','terr_hier_ csv '[Area]),

        USERELATIONSHIP('terr_hier_csv'[TERR_NAME],Territory[TERR_NAME])),

    CALCULATE(AVERAGEX(SUMMARIZE('Lookup_Table','Lookup_Table'[Use Case Latest],"toAverage",[Suggestions_SalesAlert]),[Suggestions_SalesAlert]),

           Territory[TERR_NAME] = _selected)

)

RETURN _result

[v-xiandat-msft · Accepted solution]

Hi @chinni611 ,

The problem seems to be related to the interaction between slicers and visuals. When you select an area or region, the measure behaves unexpectedly.

To achieve your goal, consider creating an unrelated table that lists all available city names. This way, when you choose any value from the slicer, the table visual won’t be filtered by it.

You can also try this DAX:

// Measure to always show national average
National_Avg = CALCULATE(
    AVERAGEX(SUMMARIZE('Lookup_Table', 'Lookup_Table'[Use Case], "toAverage", [Suggestions_SalesAlert]), [Suggestions_SalesAlert]),
    ALL('terr_hier_csv')
)

// Measure that responds to slicer selections
Selected_Territory_Avg = CALCULATE(
    AVERAGEX(SUMMARIZE('Lookup_Table', 'Lookup_Table'[Use Case Latest], "toAverage", [Suggestions_SalesAlert]), [Suggestions_SalesAlert]),
    Territory[TERR_NAME] = _selected
)

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.