Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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)
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
95 | |
91 | |
82 | |
69 |
User | Count |
---|---|
161 | |
126 | |
121 | |
109 | |
95 |