Adjust Measure Based on Slicer

[rachaellearns]

Hi Friends, 

I am trying to calculate a school and district's "Performance Index" in Power BI. 

1) Calculate the percentage of tests in each subject that fall into a given performance level (limited, proficient, advanced).

2)Assign points based on the percentage of tests in each performance level. (For example, advanced is worth 2 points, proficient is worth 1, and limited is worth 0.5. A school with 50% of students in the Limited category, 30% of students in the Proficient category, and 20% of students in the Advanced category would earn 95 points, because (50 x 0.5)+(30 x 1) + (20 x 2) = 95 points.)

My data is formatted as follows 

student_id	subject	term	school_year	performance_level	special ed	ell
1234	Math	Fall	2022	Limited	Not SPED	ELL
1234	Reading	Fall	2022	Proficient	SPED	ELL
5678	Math	Winter	2022	Advanced	Not SPED	Not ELL
5678	Reading	Winter	2022	Proficient	SPED	Not ELL
1357	Math	Fall	2022	Limited	Not SPED	ELL
1357	Reading	Fall	2022	Limited	SPED	Not ELL

To do this, I created the following meaures. These measures only work when the referenced columns are included in the visual. 

percentage = 
VAR _count = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[subject],'Table'[school_year], 'Table'[term], 'Table'[performance_level)) + 0
VAR _total = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[subject],'Table'[school_year], 'Table'[term]))
VAR _percentage = DIVIDE(_count,_total)
RETURN
_percentage

points = 
VAR _subject= MAX('Table'[subject])
VAR _year = MAX('Table'[school_year])
VAR _term = MAX('Table'[term])
VAR _Advanced = SUMX(FILTER('Table','Table'[subject]=_subject&&'Table'[school_year]=_year &&'Table'[term]=_term && 'Table'[performance_level]="Advanced"),[percentage])+0
VAR _Proficient = SUMX(FILTER('Table','Table'[subject]=_subject&&'Table'[school_year]=_year && 'Table'[term]=_term && 'Table'[performance_level]="Proficient"),[percentage])+0
VAR _Limited = SUMX(FILTER('Table','Table'[subject]=_subject&&'Table'[school_year]=_year &&'Table'[term]=_term &&'Table'[performance_level]="Limited"),[percentage])+0
VAR _Point = (_Advanced*2+_Proficient+_Limited*0.5)*100
RETURN
_Point

I then have slicers for the special education and ell (english language learner) fields. Ideally, I would like the calculation to perform for only those students who are selected (if SPED is selected from the special education dropdown, the calculation happens for only special education students. if ELL is selected from the ELL dropdown, the calculation happens for only ELL students. If nothing is selected, all students are included.) Does anyone know how I can do this? My head is swimming. 

[halfglassdarkly]

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)

[halfglassdarkly]

Hi Rachael, as long as your measure is being used on a page where it is inheriting filter context for [Subject], [School year], [Term], and [Performance level] either from your slicers OR other visual/page filters OR from your table / visual (from those fields being included) you shouldn't need to specify these in ALLEXCEPT. You should be able to simplify your percentage measure to:

percentage = DIVIDE(COUNTROWS('Table'),CALCULATE(COUNTROWS('Table'),All()),0)

Without having tested, I think you should should then be able to simplfy points as:

points = 
VAR _Advanced = Calculate([percentage],'Table'[performance_level]="Advanced")
VAR _Proficient = Calculate([percentage],'Table'[performance_level]="Proficient")
VAR _Limited = Calculate([percentage],'Table'[performance_level]="Limited")
VAR _Point = (_Advanced*2+_Proficient+_Limited*0.5)*100
RETURN
_Point

If you're not inheriting filter context from the slicer selection or elsewhere for a particular field like School Year it will simply be ignored in the calculation.