The setting is educational scores for a school. I have a slicer that represents the current pupil years. For example Johnny B is a pupil in the current Year 6. In the dataset, that group of pupils have scores from that year and previous years.
For the current pupil group (e.g. Year 6) I want to calculate, for each of their historical years, a percentage using the column OnTrackName as follows:
OnTrack % := Count pupils with OnTrackName = “On Track” divided by all pupils
By selecting Year 6 on the slicer, I can then see, on a visual, the percentage of ‘On Track’ for Johnny B’s classmates scores for the current year and also for all the previous years (as a bar chart).
When I select Year 5 on the slicer, I can see, on a visual, the scores for the current Year 5 group for that year (i.e. Year 5) and all previous years (i.e. Years 1 to 4).
The calculations are different therefore depending on the value of the slicer (i.e. which current year we are looking at).
I do not want a disconnected table for the slicer because it slices on other visuals on the page as a natural dimension.
Because the calculations are different for each current year, I was imagining that I would need a SWITCH statement. Below is a simple extract which just counts the rows for each relevant year without going as far as calculating a percentage:
OnTrack =
VAR SlicerYear = VALUES(PupilYear[Year])
RETURN
SWITCH(TRUE(),
// if the current year from the slicer is Year 6,
SlicerYear = "Year 6",
CALCULATE( COUNTA(AssessData[OnTrackName]),
UNION(
// get Year 6 for the current year, i.e year - 0 which is when AgeFromLatestYear = 0
CALCULATETABLE( AssessData, ALL(PupilYear), AssessData[PupilYearID]=600, AssessData[AgeFromLatestYear]=0),
// get Year 5 for the previous year, i.e year - 1
CALCULATETABLE( AssessData, ALL(PupilYear), AssessData[PupilYearID]=500, AssessData[AgeFromLatestYear]=1),
// get Year 4 for the year - 2
CALCULATETABLE( AssessData, ALL(PupilYear), AssessData[PupilYearID]=400, AssessData[AgeFromLatestYear]=2)
// etc for years 3, 2 and 1
)
),
// if the current year from the slicer is Year 5,
SlicerYear = "Year 5",
CALCULATE( COUNTA(AssessData[OnTrackName]),
UNION(
CALCULATETABLE( AssessData, ALL(PupilYear), AssessData[PupilYearID]=500, AssessData[AgeFromLatestYear]=0),
CALCULATETABLE( AssessData, ALL(PupilYear), AssessData[PupilYearID]=400, AssessData[AgeFromLatestYear]=1)
// etc for Years 3, 2 and 1.
)
),
BLANK()
)
However this doesn’t produce the right results. I want, for current Year 6,
Y4 5 (Year 4 in AcademicYear 18)
Y5 17 (Year 5 in AcademicYear 19)
Y6 37 (Year 6 in 20)
Instead I only get 37 for Year 6 and blanks for the other 2 years.
For current Year 5, I want
Y4 22 (Year 4 in 19 = 22)
Y5 22 (Year 5 in 20 = 22)
Y6 0 (no rows as in the future)
Here I get 22 for Year 5 but blank for Year 4.
I have included a link to the pbix file and an Excel file with the data. This is a small section of a much larger dashboard and dataset.
Any help or advice would be gratefully received! Thanks in advance!
Excel Sample workbook
Power BI Sample file
