Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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
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
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]
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |