Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Group by calculation

Please help me to findout the 'status' calculated column. I have attached data sample with calculation:

 I tried:

1st Calculated column:(DR_Stud)
VAR CC=FactStudentRecord[InSession]
RETURN
CALCULATE(VALUES(FactStudentReadiness[StudId]),FILTER(ALL(FactStudentReadiness[SchoolId]),CC=7 || CC=8 || CC=9))


2nd Calculated Measure:
School %:=
VAR ALL_STUD= DISTINCTCOUNT(FactStudentRecord[StudId])
VAR DR_STUDENT = COUNT([DR_Stud])
VAR D = (DR_STUDENT/ ALL_STUD)*100

RETURN
ROUND(D,2)

 

 

Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi Anonymous,

    Based on my test, you could refer to below formula:

    Schoolperform = ROUND(
                DIVIDE(CALCULATE(COUNT(FactStudentRecord[SchoolId]),FILTER('FactStudentRecord','FactStudentRecord'[Insession]>=7&&'FactStudentRecord'[SchoolId]=EARLIER(FactStudentRecord[SchoolId]))),
                CALCULATE(COUNT(FactStudentRecord[StudId]),FILTER('FactStudentRecord','FactStudentRecord'[SchoolId]=EARLIER(FactStudentRecord[SchoolId]))))*100,2)
    Status = IF(FactStudentRecord[Schoolperform]<=60,"Fine",
        IF(FactStudentRecord[Schoolperform]<=75,"AVG","Good"))

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

2 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

    Based on my test, you could refer to below formula:

    Schoolperform = ROUND(
                DIVIDE(CALCULATE(COUNT(FactStudentRecord[SchoolId]),FILTER('FactStudentRecord','FactStudentRecord'[Insession]>=7&&'FactStudentRecord'[SchoolId]=EARLIER(FactStudentRecord[SchoolId]))),
                CALCULATE(COUNT(FactStudentRecord[StudId]),FILTER('FactStudentRecord','FactStudentRecord'[SchoolId]=EARLIER(FactStudentRecord[SchoolId]))))*100,2)
    Status = IF(FactStudentRecord[Schoolperform]<=60,"Fine",
        IF(FactStudentRecord[Schoolperform]<=75,"AVG","Good"))

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

       

      As per your guide line I have created calculated column and working fine.

      I have created measure and there is a configuration table that contains minimum/maximum range and this table contains status column( performance labels) as well to show label wise school count. There is no relationship  between configuration and other table. Ex:

      Status

      MaxValue

      MinValue

       

      Excellent

      100

      70

       

      Average

      70

      50

       

      OK

      50

      0

       

       

      Expectation: (There are 50 schools)

      Excellent – 10, Average – 35, OK – 5


      Measure1 :=

      VAR RS =

          CALCULATE (

              COUNT ( FactStudent [Schoolid] ),

              FILTER (

                  FactStudent,

                  AND (

                      FactStudent [InSession] >= 7,

                      FactStudent [InSession] <= 9

                  )

              )

          )

      VAR SS =

          COUNT ( FactStudent[StudId] )

      RETURN

          CALCULATE (

              DISTINCTCOUNT ( FactStudent[SchoolId] ),

              FILTER (

                  ADDCOLUMNS (

                      DimSchool,

                      "SchoolLevelP", CALCULATE (

                          DIVIDE ( RS, SS, 0 ) * 100,

                          CALCULATETABLE (FactStudent)

                      )

                  ),

                  COUNTROWS (

                      FILTER (

                          Configuration,

                          [SchoolLevelP] >= Configuration[MinValue]

                              && [SchoolLevelP] < Configuration[MaxValue]

                      )

                  )

                      > 0

              ),

              FILTER (

                  FactStudent,

                  FactStudent[SchoolId] = FactStudent[SchoolId]

              )

          )