I'm having trouble with creating a dynamic reference line when changing filters.
What' i'm looking for is a reference line for last year's data. Which will change during a presentation when selecting regions and sub offices. We have mutliple years of data, but i'd like to compare it against 2014's ROI data for the company as a whole as well as regional and local office specific.
The Camp Yr is a column header where the data is YYYY value, one of which is 2014.
Below is what i concocted in the reference line expression.
(sum( {$<[Camp Yr] ={“2014”}>}([Gross Sales (Campaign)]) )
-
(sum( {$<[Camp Yr] ={“2014”}>}([Campaign Costs])))
/
(sum( {$<[Camp Yr]={“2014”}>}([Campaign Costs]))))
whereas the rest of the guage would look at the filtered data.
