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

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]

        )

    )