Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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_idsubjectschool_yearperformance_level
1234Math2022Limited
1234Reading2022Proficient
5678Math2022Advanced
5678Reading2022Proficient
1357Math2022Limited
1357Reading2022Limited

 

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!

Need Help

3 Replies

  • aj1973's avatar
    aj1973
    Community Champion
    3 years ago

    Hi Anonymous 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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?