Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
rachaellearns
Frequent Visitor

Adjust Measure Based on Slicer

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_idsubjecttermschool_yearperformance_levelspecial edell
1234MathFall2022LimitedNot SPEDELL
1234ReadingFall2022ProficientSPEDELL
5678MathWinter2022AdvancedNot SPEDNot ELL
5678ReadingWinter2022ProficientSPEDNot ELL
1357MathFall2022LimitedNot SPEDELL
1357ReadingFall2022LimitedSPEDNot 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. 

2 REPLIES 2
halfglassdarkly
Responsive Resident
Responsive Resident

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_2-1679793078544.pnghalfglassdarkly_3-1679793105983.png

 

halfglassdarkly
Responsive Resident
Responsive Resident

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.