Forum Discussion
Group by category calculation
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 ( |
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]
)
)
hi, Anonymous
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
3 Replies
hi, Anonymous
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
hi, Anonymous
It's pleasant that your problem has been solved, could you please mark my reply as Answered?
Best Regards,
Lin