cancel
Showing results for
Did you mean: Frequent Visitor

## Help making a measure that references multiple tables

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

1 ACCEPTED SOLUTION  Community Support

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 _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])
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.  Community Support

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