Footer Calculations in a Crosstab not working as expected

[nanefy]

Hi,

I am really hoping someone can assist me with what I assumed would be a really simple count calculation, but has become a real pain - probably because I am a bit of a noob. I have hunted through this forum and all over google and I cannot find an answer to my problem.

So, I have a Crosstab that has been created using two queries and ultimately the report is tracking pupil progress for each course. I have attached a screenshot to give an idea of what I am working with. I’ve had to fill it with fake data for data protection reasons.

So in the screenshot attached, the rows contain the pupil level data and the columns contain course level data. The columns are broken down by Course AND Level. In the body of the Crosstab, is a measure with the formula as follows:

=If(IsNull([Working Grade]) And ([Level] InList (“NAT3”; “NAT4”;“NAT5”;“HIGH”;“ADVH”));0;[Working Grade])

The Y/N columns (I know they aren’t officially columns, but actually rows) are each calculated using a bunch of measures (I don’t know if you need this info, but I’m including it just in case)

Column1 (after the first ‘columns’ Forename, Surname and ID)
=If(Sum([vPassAC] Where ([Level] InList (“NAT3”;“NAT4”;“NAT5”;“HIGH”;“ADVH”)))>4;“Y”;“N”)

Column2
=If(Sum([vPassAC] Where ([Level] InList (“NAT4”;“NAT5”;“HIGH”;“ADVH”)))>4;“Y”;“N”)

Column3
=If(Sum([vPassAC] Where ([Level] InList (“NAT5”;“HIGH”;“ADVH”)))>4;“Y”;“N”)

Column4
=If(Min([Working Grade]Where(Left([Course];4) InList (“C724”; “C824”)))<7;“Y”;“N”)

Column5
=If(Min([Working Grade]Where(Left([Course];4) InList(“C744”;“C844”)))<7;“Y”;If(Min([Working Grade]Where(Left([Course];4) InList(“C747”;“C847”)))<7;“Y”;“N”))

Column6
=If(Concatenation([vEnglish@3A-C];[vMaths@3A-C])=“YY”;“Y”;“N”)

Now, what I want to do, is have 3 footer calculations for each of those Y/N ‘Columns’. The first footer calculation just counts how many rows there are in the report - that one is easy, I can just do a count of the ID’s. The second footer calculation is to show how many “Y”'s there are in each ‘Column’. The third footer calculation is just the second calculation/first calculation to work out the percentage. (the cells highlighted in green would be where I would start the footer calculations)

I figured it would be simple:

=Count([ID] Where(Column1 = “Y”))

But nope - I have tried about 50 different calculations, using a variety of syntaxes, but I get one of the following values: 0, 1, 199 (which is the total number of rows) or 8000 (not exactly 8000, but some crazy high number). There should be 199 rows total and the number of "Y"s should be lower than 199, but I cannot for the life of me get it to work. I don’t know if its to do with Calculation Contexts or if it’s something else entirely, but please, someone put me out of my misery

[nachtaktiv]

you have to do this with calculation context.

pay attention to parenthesis and input/output-context.

… and …  use variables instead of writing your formula into the webi-block/cell