Here is a sample of the data I will be using:
Date Score Student Category Jan-20 0.8 Jim Above 80 Feb-20 0.75 Jim Below 80 Mar-20 0.7 Jim Below 80
Jan-21 0.85 Jim Above 80 Feb-21 0.60 Jim Below 80 Mar-21 0.79 Jim Below 80 Jan-20 0.9 Andrea Above 80 Feb-20 0.95 Andrea Above 80 Mar-20 0.92 Andrea Above 80 Jan-20 0.6 Max Below 80 Feb-20 0.65 Max Below 80 Mar-20 0.7 Max Below 80
I would like to be able to show how many students fall into the above 80 and below 80 scorecards depending on what filter i choose. For example, if i filtered on jan and feb 2020, it would show 2 students below 80 and 1 student above 80. If I only filtered on Jan 2020, it would show 2 students above 80 and 1 below 80.
I was able to create a solution that only works for one year using SUMMARIZECOLUMNS and then doing a filtered count of these columns, however this breaks down once I have more than one year.
Scores Above 80 =
COUNTROWS(
FILTER(
SUMMARIZECOLUMNS(
'Table'[Student],
'Table'[Date of call].[Year],
"Avg Score", AVERAGE('Table'[Score])
),[Avg Score]>0.80
))
If I remove the time column, it doesn't interact with the slicers properly and keeps breaking
Scores Above 80 =
COUNTROWS(
FILTER(
SUMMARIZECOLUMNS(
'Table'[Student],
"Average",AVERAGE('Table'[Score])
),[Average] > 0.80
))
Perhaps there is a much easier way of approaching this, but I'm stuck with my answer. Any help would be greatly appreciated
