Row Labels in Pivot Table

[Griswald]

I am building an exams analysis pivot table, dealing with GCSE Grades which comprise A*/A/B/C/D/E/F/G/U/X.

I put the Subject Grade field in the Row Label and again in the Values to give a count of grade and all works well with all grades in the field being represented!

However I want to show a second subject in Values, and this causes problems! If the second subject has a full range of possible grades, and the first has a subset, then only the subset are shown as Row Labels and grades from the second set are not shown. The whole thing will be driven by Slicers and it is therefore not possible to predict which field will have the largest range of values.

If you add the second subject to the Row Labels you get a horrible mess of subtotals and subsidiary values, not pretty and not useable in a pivot chart!

Can anyone advise how I can amalgamate the row labels to show the range of all values from both fields?

Thanks

 

[VeryForgetful]

Hi Griswald,

I would suggest trying to add the second subject to the row section then double click the button for that field and change Subtotals from Automatic to None otherwise as you say it's going to look a mess.

Unless you have it showing in the row area you will have no idea what those grades relate to.

Any chance you can post a screenshot?

 

[Griswald]

Hi there,

Thanks for taking the trouble to help!

I have attempted to include a screen shot for you to look at, of some sample data, English marks and Maths marks!

You will see there are some Maths marks that do not make it to the Chart Axis!

I have disabled subtotals on both row values, and that makes no difference.

Any further suggestions would be appreciated.

 

[Griswald]

That image url did not work!
Here is a link to the file in Dropbox!https://www.dropbox.com/s/rf1aobpa15r2ztc/Screen Shot 2015-03-08 at 16.35.17.png?dl=0

 

[VeryForgetful]

Instead of a PivotTable I would maybe try a formula approach with a countif.

Have a quick look at the attached workbook to see if this is any use. Will be easier to construct a graph from it as well. https://www.dropbox.com/s/qfce9amu1y5fhyt/example.xls?dl=0