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]
)
)
Solved! Go to 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
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
Thanks! Its working as expacted result.
hi, @Tejesh_Gour
It's pleasant that your problem has been solved, could you please mark my reply as Answered?
Best Regards,
Lin
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
Join the biggest FREE Business Applications Event in LATAM this February.
User | Count |
---|---|
217 | |
57 | |
49 | |
46 | |
45 |
User | Count |
---|---|
268 | |
211 | |
113 | |
82 | |
71 |