1 of 1 people found this helpful
Max, you can't do this if you have a filter applied to the sheet that selects only one postcode. You will need to use page shelf instead or do something else. By definition, filter will remove all data for other postcodes from the sheet, so you can't calculate national average.
It would help if you could post a workbook with some sample data.
Thanks Dimitri. That makes sense I guess. Unfortunately I can't post the workbook as some of the data is sensitive. I'll have a look at how to use page shelf.
To create a reference line based on the whole dataset, you cannot use filters to exclude out values, this changes the overall average.
What I would do is create a parameter for the "SelectedPostcode" or "SearchPostcode". Then create a calculated field called DisplayPostcode. Enter this formula:
IF [Postcode] = [SearchPostcode] then 'show' else 'hide' end
Then add this new calculated field to your worksheet. This will break down your view into two sections. Right click on the Hide pane and select hide. This will give you the bar graphs for the chosen postcode.
You can then create a calculated field using a Window_avg or other window function, add it to your level of data and use it as a reference line.
Note: Using the hide function over a filter allows the window functions to use the whole dataset.