Group by calculation

[Tejesh_Gour]

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)

[v-danhe-msft · Accepted solution]

Hi @Tejesh_Gour,

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

[Tejesh_Gour]

Hi @v-danhe-msftv,

Thanks for your help! 

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]

        )

    )