Forum Discussion

diogobraga2's avatar
diogobraga2
Icon for Helper IV rankHelper IV
6 years ago
Solved

Calculate Straight or Weighted Average for the Total

The dataset has grade level data for schools (subset shown below). The goal is to calculate percentage of total for each grade AND for the whole school using this dataset.  Percent Enrolled = Total...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi diogobraga2 ,

     

    As the error message, In your formual,  DISTINCTCOUNT should not be used like thay, please refer to my formula again.

    Measure 2 = 
SUMX ( 'Table', [Measure] ) / DISTINCTCOUNT ( 'Table'[Grade] )

     

    Or you can share your pbix to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

diogobraga2 can you share what measure you currently used to calculate average

  • diogobraga2's avatar
    diogobraga2
    Icon for Helper IV rankHelper IV
    6 years ago

    I tried avereging the calculation listed on the post: Percent Enrolled = Total Students / Target

     

     

    • diogobraga2's avatar
      diogobraga2
      Icon for Helper IV rankHelper IV
      6 years ago
      This is the actual calcution:
      % Total Enrolled M = if(student_status_count[Total Students M]/student_status_count[Target M]>0,student_status_count[Total Students M]/student_status_count[Target M],0)
       

      • v-frfei-msft's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi diogobraga2 ,

         

        We can make it divide by the distinctcount of [Grade]. Here I have created a sample for your reference.

        Measure = 
SUM ( 'Table'[Target] ) / SUM ( 'Table'[Total Students] )

        Measure 2 = 
SUMX ( 'Table', [Measure] ) / DISTINCTCOUNT ( 'Table'[Grade] )