I've got a rather complex workbook. My dashboard is made up of a map, bar/bullet graphs, and a pane across the top where I'll be providing summary values like averages, number of cities selected, etc.
Users select a city they want to compare with. This feeds into a calculation that basically sets the selected city as 1, and all else as 2.
I'm not using any quick filters - everything is parameter based because there are several filters I'm offering such as "same county", "same region", "within 25 miles", "within 50 miles", etc. where I have feeder calculations that compare each city's value to the value of selected city, then the final calculation toggles between the filters based on which "filter choice" is selected in a parameter (the following calculation is on the filter shelf of the map, bar, and bullet graphs, showing all "True" values).
case [Show Cities:]
when "Less than 100 mi" then [Less than 100 mi]
when "Less than 25 mi" then [Less than 25 mi]
when "Less than 50 mi" then [Less than 50 mi]
when "Same County" then [Same County]
when "Same Region" then [Same Region]
when "All" then TRUE
This works fine, so long as the "city" granularity is in the view as in the bar and bullet graphs, but for the summary values where I'd like to show based on the cities that are in the other graphs, I can't place city on the detail shelf because it will disaggregate the measures, and there's no "city filter" I can apply to the sheet as users are not selecting actual cities. Because the calculations underlying these are table calculations
(Results are computed along City, sorted ascending by Min. Selected City)
LOOKUP(MIN([Primary County]), FIRST()) == Attr(Primary County])
They require to "compute using" the city field, which isn't in the view for the summary data sheet.
Is there any way to hack this using hidden sheets, or is there something similar I'm missing? My initial idea was to create table calculations for all of the values that I want (total(average(value)), window_sum(countd(city)), etc) so the same value appears for each city, then filter to top 1 and hide the rows, but that won't work. I can try to dummy up some data to attach a workbook, but wanted to see if anyone else has any ideas, as it will take some time to recreate each calculation.