3 Replies Latest reply on Feb 7, 2012 2:55 PM by Nathan Krisanski

# Need help with reference lines on bullet chart

I have a dataset for all the postcodes in Australia e.g. Household income by category e.g. Under \$20k, \$20k-\$60k etc.  I have created a horizontal bar chart showing the data for one postcode (using a filter).  I now want to create a reference line for each bar showing the national average i.e. the average of the total dataset for each column.  How do I do this?

• ###### 1. Re: Need help with reference lines on bullet chart

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.

1 of 1 people found this helpful
• ###### 2. Re: Need help with reference lines on bullet chart

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.

• ###### 3. Re: Need help with reference lines on bullet chart

Hi Max,

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.