Forum Discussion
Adjust Measure Based on Slicer
Apologies, having re-read your problem statement and tested with your example data I can see this was more complicated.
I've tested the following measures and I think they are mostly doing what you are looking for at record level in the visual table, I'm just not sure whether the total row meets your requirements. The IF(ISINSCOPE checks in the measure are an attempt to ensure the totals are calculated correctly. I've used Summarize to calculate the values relevant to each performance_level where they would otherwise not inherit this context by default for the total row. If totals weren't required the measures could be simplified substantially. The Score Total looks odd compared to the scores at row level because it's calculating based on the % of the overall total records returned weighted by each performance level, without the context from year, term, subject.
Percentage =
VAR _subject= MAX('Table'[subject])
VAR _year = MAX('Table'[school_year])
VAR _term = MAX('Table'[term])
VAR _level = MAX('Table'[performance_level])
Var _count = if(ISINSCOPE('Table'[student_id]),Countrows(SUMMARIZE('Table','Table'[subject],'Table'[school_year],'Table'[term],'Table'[student_id])),Countrows(SUMMARIZE(ALLSELECTED('Table'),'Table'[subject],'Table'[school_year],'Table'[term],'Table'[student_id])))
Var _Advanced = if(ISINSCOPE('Table'[student_id]),if(_level="Advanced",CALCULATE(COUNTROWS('Table'),ALLSELECTED('Table'),'Table'[school_year]=_year,'Table'[term]=_term,'Table'[subject]=_subject),0),COUNTROWS(SUMMARIZE(FILTER('Table','Table'[performance_level]="Advanced"),'Table'[subject],'Table'[school_year],'Table'[term],'Table'[performance_level],'Table'[student_id])))
Var _Proficient = if(ISINSCOPE('Table'[student_id]),if(_level="Proficient",CALCULATE(COUNTROWS('Table'),ALLSELECTED('Table'),'Table'[school_year]=_year,'Table'[term]=_term,'Table'[subject]=_subject),0),COUNTROWS(SUMMARIZE(FILTER('Table','Table'[performance_level]="Proficient"),'Table'[subject],'Table'[school_year],'Table'[term],'Table'[performance_level],'Table'[student_id])))
Var _Limited = if(ISINSCOPE('Table'[student_id]),if(_level="Limited",CALCULATE(COUNTROWS('Table'),ALLSELECTED('Table'),'Table'[school_year]=_year,'Table'[term]=_term,'Table'[subject]=_subject),0),COUNTROWS(SUMMARIZE(FILTER('Table','Table'[performance_level]="Limited"),'Table'[subject],'Table'[school_year],'Table'[term],'Table'[performance_level],'Table'[student_id])))
RETURN
if(ISINSCOPE('Table'[student_id]),Divide(_count,_Limited,0),Divide(_Limited,_count,0))+
if(ISINSCOPE('Table'[student_id]),Divide(_count,_Proficient,0),Divide(_Proficient,_count,0))+
if(ISINSCOPE('Table'[student_id]),Divide(_count,_Advanced,0),Divide(_Advanced,_count,0))Score =
VAR _subject= MAX('Table'[subject])
VAR _year = MAX('Table'[school_year])
VAR _term = MAX('Table'[term])
VAR _level = MAX('Table'[performance_level])
Var _count = if(ISINSCOPE('Table'[student_id]),Countrows(SUMMARIZE('Table','Table'[subject],'Table'[school_year],'Table'[term],'Table'[student_id])),Countrows(SUMMARIZE(ALLSELECTED('Table'),'Table'[subject],'Table'[school_year],'Table'[term],'Table'[student_id])))
Var _Advanced = if(ISINSCOPE('Table'[student_id]),if(_level="Advanced",CALCULATE(COUNTROWS('Table'),ALLSELECTED('Table'),'Table'[school_year]=_year,'Table'[term]=_term,'Table'[subject]=_subject),0),COUNTROWS(SUMMARIZE(FILTER('Table','Table'[performance_level]="Advanced"),'Table'[subject],'Table'[school_year],'Table'[term],'Table'[performance_level],'Table'[student_id])))
Var _Proficient = if(ISINSCOPE('Table'[student_id]),if(_level="Proficient",CALCULATE(COUNTROWS('Table'),ALLSELECTED('Table'),'Table'[school_year]=_year,'Table'[term]=_term,'Table'[subject]=_subject),0),COUNTROWS(SUMMARIZE(FILTER('Table','Table'[performance_level]="Proficient"),'Table'[subject],'Table'[school_year],'Table'[term],'Table'[performance_level],'Table'[student_id])))
Var _Limited = if(ISINSCOPE('Table'[student_id]),if(_level="Limited",CALCULATE(COUNTROWS('Table'),ALLSELECTED('Table'),'Table'[school_year]=_year,'Table'[term]=_term,'Table'[subject]=_subject),0),COUNTROWS(SUMMARIZE(FILTER('Table','Table'[performance_level]="Limited"),'Table'[subject],'Table'[school_year],'Table'[term],'Table'[performance_level],'Table'[student_id])))
RETURN
(if(ISINSCOPE('Table'[student_id]),Divide(_count,_Limited,0),Divide(_Limited,_count,0))*.5*100)+
(if(ISINSCOPE('Table'[student_id]),Divide(_count,_Proficient,0),Divide(_Proficient,_count,0))*100)+
(if(ISINSCOPE('Table'[student_id]),Divide(_count,_Advanced,0),Divide(_Advanced,_count,0))*2*100)