Trying to create bar chart with 2 bars - one bar that is unaffected by slicer and one that is

[Anonymous]

Hello,

I have a measure in my report that calculates the average deal size in my sales data. I am trying to create a bar chart with 2 bars - both of them showing that measure, but one of them showing the average of the total data set and the other bar showing the average of the selected employee. In other words, I want one of the bars to be unaffected by the slicer and stay constant with the average of the entire data set and the other bar to be affected by the salesperson that is selected.

For example, in the sample report I've linked below, the average new deal for all is $68 while Mary's average new deal is $15. I want a bar chart to show Mary's $15 average and the $68 total average, and the $15 bar switches for who I have selected.

Sample Dashboard: https://www.dropbox.com/s/c43c65d11m07exo/Dummy%20Data.pbix?dl=0

The reason why I'm having trouble is because the DAX for my average measure is complicated, as follows: 

Average New Business for All ($) = var _table=SUMMARIZE(FILTER(sheet1,Sheet1[Type]="New"),Sheet1[Deal #],Sheet1[Type],"_value",CALCULATE(SUM(Sheet1[Price]))) return AVERAGEX(_table,[_value])+0

Thank you for your help on this.

[Greg_Deckler · Accepted solution]

You should be able to use ALLEXCEPT or REMOVEFILTERS in a measure to exempt one of the columns/bars.

[Anonymous]

Removefilters worked perfectly. Thank you!