5 Replies Latest reply on Feb 8, 2017 11:12 AM by Shinichiro Murakami

# How do I create a calculated field which is the average of selected rows of data?

I have created a box plot of University league table scores over time and have picked out one university to show on a trend line across the chart.  I would like to add another reference line that shows the average of a sub-set of universities (for example, Bolton, Cumbria and Manchester Metropolitan) across the chart, but for the life of me cannot work out how to do it.  I'm guessing I need to add another calculated field but I can't seem to combine the IF statement with an aggregation.  What formula to I need in the calculated field?

Vicci

• ###### 1. Re: How do I create a calculated field which is the average of selected rows of data?

Hi Vicci,

You can create "Set" from University Filed.

Then create calculated field.

// Float is to make the field type to dicimal number to make the axis synclonized

[Selected Score]

FLOAT(if [Selected Univ.]then [Overall Score] end)

You can change the University in the list with editing the Set of "Selected Univ.".

Thanks,

Shin

• ###### 2. Re: How do I create a calculated field which is the average of selected rows of data?

Hi Shin

Unfortunately when I try your suggestion, my set of universities display as separate lines on the chart, rather than one line showing the average of the set (I tried changing the Measure from Sum to Avg, but this hasn't worked).  I have looked at the example workbook you sent back, but I can't work out out how made the Selected Univ. set show only as one line?  Am I missing a step? Many thanks for all your help!

Vicci

• ###### 3. Re: How do I create a calculated field which is the average of selected rows of data?

Not exactly sure what you are seeing , but could you try this one?

Please attach screen shot or packagd workbook with failure if you still have trouble.

Thanks,

Shin

• ###### 4. Re: How do I create a calculated field which is the average of selected rows of data?

Thanks for all your help, Shin.  It was because the University pill was in the Measure Values card.  Once I took that out, it was fine.

V

• ###### 5. Re: How do I create a calculated field which is the average of selected rows of data?

Sounds good.

Shin