powerbi line chart - have a line that is fixed.

[semidevil]

I have a time series line chart that displays data over time for a particular state.  I also have a filter that will allow me to filter for cities, so I can see data over time for a particular city instead

what I want to add is another  line on my chart for the state, but have it in such a way that it is not affected by my filters.

Basically,  my result would be a line chart with 2 lines:  1 line that will show me data to a city(depending on my filter), and another line for the overall state line.  That way, I can compare "city" vs overall "state."

I thought adding a new column and aggregating the entire state data would do the trick, but it ended up being literally a flat line.

[Phil_Seamark · Accepted solution]

HI @semidevil

For your 'fixed' line, use a calculated measure and use the CALCULATE/ALL/ALLSELECTED functions to control the effect of external filters on the expression.  It's possible to write your calculation so it disregards external filters.

[semidevil]

Thank you @Phil_Seamark.

  Here's the measure I attempted, and when I toggled my city slicer, the line still continue to move even though I expected it to stay at the 'state' level.

Measure= CALCULATE(SUM(Value),FILTER('Table1, Table1[Column1]="variable1"),FILTER(Table2,Table2[Column1]="variable2" || Table2[Column1]= "variable3"),ALL(City[City]))

I tried All, AllExcept, but it still did not seem to work.  I want all the rest of the filters to in and out of the query to still work....except the "city" slicer.

EDIT:  I just realized that when I removed the "Filter" inside my query, it seemed to solve the problem, and I just had to execute the filter in the GUI (visual level filter).  Is this by design?  I would like to have at least 1 filter in my query because my end goal is to have 3 line charts:  line 1 based on a filter, line 2 based on another filter from the same column, and line 3, the 'state' average.