Help making a measure that references multiple tables

[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
1234	Math	Fall	2022	Limited
1234	Reading	Fall	2022	Proficient
5678	Math	Winter	2022	Advanced
5678	Reading	Winter	2022	Proficient
1357	Math	Fall	2022	Limited
1357	Reading	Fall	2022	Limited

To do this, I created the following meaures. These measures work when I only include the fields from the above dataset in my table 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 a "sort" table that links to the above table (aka 'Table').  I use the 'term' column from the below table in my visuals and slicers. 

term	order
Fall	1
Winter	2
Spring	3

Unfortunately, when I do this, the calculations above become messed up. Does anyone have any idea how to fix this? 

[v-tangjie-msft · Accepted solution]

Hi @rachaellearns ,

According to my research and tests, the reason why the slicer does not filter properly after creating a "sorted" table is that you add a +0 after the output value of each variable, resulting in an abnormal filtering after passing through another table as a slicer, with a value of 0 instead of blank, you can try removing +0 from the two measures.

percentage = 
VAR _count = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[subject],'Table'[school_year], 'Table'[term], 'Table'[performance_level)) 
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])
VAR _Proficient = SUMX(FILTER('Table','Table'[subject]=_subject&&'Table'[school_year]=_year && 'Table'[term]=_term && 'Table'[performance_level]="Proficient"),[percentage])
VAR _Limited = SUMX(FILTER('Table','Table'[subject]=_subject&&'Table'[school_year]=_year &&'Table'[term]=_term &&'Table'[performance_level]="Limited"),[percentage])
VAR _Point = (_Advanced*2+_Proficient+_Limited*0.5)*100
RETURN
_Point

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.