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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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_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.

2 REPLIES 2
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)``````

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors