Group by category calculation

[Tejesh_Gour]

Please help me to find correct answer:
Fact Table: FactStudent

SchoolId	StudId	InSession
S1	11	5	SchoolLevelP= DISTINCT COUNT(StudId (NOTE: Those are in insession 7,8,9) / TotalStudID(DISTINCT)*100
S1	12	6
S1	13	4
S1	14	8
S1	15	7
S1	16	9
S1	17	8
S1	18	0	Status =  FILTER (                     Configuration,                     [SchoolLevelP] >= Configuration[MinValue]                         && [SchoolLevelP] < Configuration[MaxValue]                 )
S1	19	9
S1	20	8
S1	21	9
S1	22	9
S1	23	9
S1	24	7
S1	25	3
S2	111	8
S2	112	9
S2	113	4
S2	114	4
S2	115	8
S2	116	7
S2	117	7
S2	118	9
S2	119	9
S2	120	8

I have created measure below and there is a configuration table for minimum/maximum range along with status column (performance labels). to show label wise school count. Ex:
Table: Configuration

Status	MaxValue	MinValue
Excellent	100	70
Average	70	50
OK	50	0

Expected result: (There are 50 schools in fact table)

Status	School Count
Excellent	10
Average	35
OK	5

Below I tried to achieve by calculated measure.

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]

        )

    )

[v-lili6-msft · Accepted solution]

hi, @Tejesh_Gour

You may try this formula

Measure2 = 
CALCULATE (
    DISTINCTCOUNT ( FactStudent[SchoolId] ),
    FILTER (
        ADDCOLUMNS (
            DimSchool,
            "SchoolLevelP", CALCULATE (
                DIVIDE (
                    CALCULATE (
                        COUNT ( FactStudent[Schoolid] ),
                        FILTER (
                            FactStudent,
                            AND ( FactStudent[InSession] >= 7, FactStudent[InSession] <= 9 )
                        )
                    ),
                    COUNT ( FactStudent[StudId] )
                ) * 100
            )
        ),
        COUNTROWS (
            FILTER (
                Configuration,
                [SchoolLevelP] >= Configuration[MinValue]
                    && [SchoolLevelP] < Configuration[MaxValue]
            )
        ) > 0
    )
)

Result:

Best Regards,

Lin

[v-lili6-msft · Accepted solution]

hi, @Tejesh_Gour

You may try this formula

Measure2 = 
CALCULATE (
    DISTINCTCOUNT ( FactStudent[SchoolId] ),
    FILTER (
        ADDCOLUMNS (
            DimSchool,
            "SchoolLevelP", CALCULATE (
                DIVIDE (
                    CALCULATE (
                        COUNT ( FactStudent[Schoolid] ),
                        FILTER (
                            FactStudent,
                            AND ( FactStudent[InSession] >= 7, FactStudent[InSession] <= 9 )
                        )
                    ),
                    COUNT ( FactStudent[StudId] )
                ) * 100
            )
        ),
        COUNTROWS (
            FILTER (
                Configuration,
                [SchoolLevelP] >= Configuration[MinValue]
                    && [SchoolLevelP] < Configuration[MaxValue]
            )
        ) > 0
    )
)

Result:

Best Regards,

Lin

[Tejesh_Gour · Accepted solution]

Thanks! Its working as expacted result.

[v-lili6-msft]

hi, @Tejesh_Gour

It's pleasant that your problem has been solved, could you please mark my reply as Answered?

Best Regards,

Lin