3 Replies Latest reply on May 1, 2018 5:57 AM by Yuriy Fal

# Reference line for 'measure names'

I have created a chart that shows how many children have passed a maths test in a particular school, but now I want to add a reference line and I'm stuck.

The complication is that the chart breaks down the results by pupil characteristics - including gender, special needs, first language - and I want to show each of these characteristics independently rather than nested. (In other words, I want to see all pupils, all boys, all girls, all pupils with special needs etc, not the usual Tableau method of boys-with-special-needs-who-speak-English, boys-with-special-needs-who-speak-a-different-language, boys-without-special-needs-who-speak-English etc). In order to do that, I have created a number of calculated fields along the lines of:

Male: IF [Gender]="M" THEN 1 END

and created the chart by dropping SUM(Male), SUM(Female) etc into the "Measures values", which is used for Rows and applying the necessary calculations.

That bit is working fine!

To make it meaningful, I want schools to be able to compare their results to the national average.

I can add a reference line across the whole chart by setting a parameter with a constant value ... but what I really want to do is to be able to set the parameter for each column individually.

I've had a look at Can I create a reference line for only one measure in a bar graph? and that looks like it would give me the answer if I had a regular dimension on the rows ... but I can't figure out how to target the parameter for a particular 'measure values' point.

If anyone can guide me on the next step, I would be really grateful!

• ###### 1. Re: Reference line for 'measure names'

Hi Stephen,

I'm a little bit foggy regarding

a 'national average' as a Parameter --

wouldn't it be a Calculation instead?

Besides, my guidance on the next step would be

making a distinct view for each Measure (one stacked bar),

then combining them all side-by-side on a dashboard.

You'd be able to make different Ref Lines for each then.

Yours,

Yuri

• ###### 2. Re: Reference line for 'measure names'

Hi Yuri,

Sorry, maybe I wasn't clear. I work for a local council, so we have a data file for all of the pupils in all of our schools. That is what I am putting into Tableau so that I can produce a report on the test results by school. This data file only covers our own schools and not those from the rest of the country – the national averages that I want to show with the reference lines are just going to be static figures that I have taken from the government's statistical release. Yes, if I had the data file for the whole country then they would be calculations, but in this situation they are just manually typed numbers.

If there's no easy way to have a variable reference line against 'measure values' then I will look into drawing each block as a separate chart, but I was trying to avoid that because of the difficulty in lining them up and ensuring that the axes are the same across all of the charts (in some cases I will want axes that scale to the content rather than just going to 100%).

Thanks,

Stephen

• ###### 3. Re: Reference line for 'measure names'

Stephen, no prob.

If there's no easy way to have a variable reference line against 'measure values'...

In Tableau, [Measure Names] is a special purpose pill (so is Measure Values).

There's no way to make [Measure Names] behave in full like a 'real' Dimension --

and this includes rendering a distinct Mark for each (Measure Name) value

and a distinct Reference Line object for each Header (per Cell).

To get what you want (at least visually) a Dimension should be used (on Columns)

with the unique values the same as the (necessary) Measure Names.

This could be done for example by UNIONing the datasource -- as many times

as the number of Measure Names (columns) to be on a view.

The [Table Names] Dimension could be used (in a pair of calculations)

for placing the Measures names as Headers on Columns (a blue pill)

and their respective Measures values on Rows (a green pill).

Yours,

Yuri