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

## Help Calculating School Performance Index in Power BI (Complicated Calculation in Power BI)

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 school_year performance_level 1234 Math 2022 Limited 1234 Reading 2022 Proficient 5678 Math 2022 Advanced 5678 Reading 2022 Proficient 1357 Math 2022 Limited 1357 Reading 2022 Limited

There will be multiple school years in this file as well - this is a simplified view.

How would I approach this in Power BI? Would it be best to do this in PowerQuery somehow, or would it be better to create a measure in DAX? A mixture of both?

PowerBI is not something I frequently use and am very much a beginner, so I appreciate any help you can provide!

1 ACCEPTED SOLUTION
Community Support

Please new two measures:

``````percentage =
VAR _count = COUNTROWS('Table') + 0
VAR _total = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[subject],'Table'[school_year]))
VAR _percentage = DIVIDE(_count,_total)
RETURN
_percentage``````
``````points =
VAR _subject= MAX('Table'[subject])
VAR _year = MAX('Table'[school_year])
VAR _Advanced = SUMX(FILTER('Table','Table'[subject]=_subject&&'Table'[school_year]=_year&&'Table'[performance_level]="Advanced"),[percentage])+0
VAR _Proficient = SUMX(FILTER('Table','Table'[subject]=_subject&&'Table'[school_year]=_year&&'Table'[performance_level]="Proficient"),[percentage])+0
VAR _Limited = SUMX(FILTER('Table','Table'[subject]=_subject&&'Table'[school_year]=_year&&'Table'[performance_level]="Limited"),[percentage])+0
VAR _Point = (_Advanced*2+_Proficient+_Limited*0.5)*100
RETURN
_Point``````

M or DAX? That is the Question

KNP's answer:

When should I favor DAX over PowerQuery for perfor...

Comparing DAX calculated columns with Power Query computed columns

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

3 REPLIES 3
Community Support

Please new two measures:

``````percentage =
VAR _count = COUNTROWS('Table') + 0
VAR _total = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[subject],'Table'[school_year]))
VAR _percentage = DIVIDE(_count,_total)
RETURN
_percentage``````
``````points =
VAR _subject= MAX('Table'[subject])
VAR _year = MAX('Table'[school_year])
VAR _Advanced = SUMX(FILTER('Table','Table'[subject]=_subject&&'Table'[school_year]=_year&&'Table'[performance_level]="Advanced"),[percentage])+0
VAR _Proficient = SUMX(FILTER('Table','Table'[subject]=_subject&&'Table'[school_year]=_year&&'Table'[performance_level]="Proficient"),[percentage])+0
VAR _Limited = SUMX(FILTER('Table','Table'[subject]=_subject&&'Table'[school_year]=_year&&'Table'[performance_level]="Limited"),[percentage])+0
VAR _Point = (_Advanced*2+_Proficient+_Limited*0.5)*100
RETURN
_Point``````

M or DAX? That is the Question

KNP's answer:

When should I favor DAX over PowerQuery for perfor...

Comparing DAX calculated columns with Power Query computed columns

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Frequent Visitor

Wow, thank you so much for the help with this! I do have one question -- what is the reason for adding a zero at the end of the SUMX calculations? Is it so it doesn't return an error in the case of a null/blank value?

Community Champion

Power Query is best for ETL, DAX is best for calculations.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### 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