Virtual columns giving wrong answers

I created an attendance register app. One of the tables has virtual columns for calculating the percentage attendance based on conditions.
Two virtual columns calculate the total lessons conducted in each semester ie

Semester 1 total with expression:
COUNT(SELECT(Semester1[LessonID], AND( ([Course Code]=[_THISROW].[Course Offered]), (YEAR([Date])=[_THISROW].[Year]))))

Semester 2 total:
COUNT(SELECT(Semester2[LessonID], AND( ([Course Code]=[_THISROW].[Course Offered]), (YEAR([Date])=[_THISROW].[Year]))))

the other virtual columns calculate attended classes by a student ie
First semester attendance:
COUNT(SELECT(StdSemester1[Lessonkey], (AND(([Student Number]=[_THISROW].[Student]), ([Course_Done] = [_THISROW].[Course Offered])))))

and Second semester attendance:
COUNT(SELECT(StdSemester2[Lessonkey], (AND(([Student Number]=[_THISROW].[Student]), ([Course_Done] = [_THISROW].[Course Offered])))))

The last virtual column calculates the percentage attendance using formula:
IFS([Period]=“Semester 1”,(([First Semester attendance]/[Semester1 total])*100), [Period]=“Semester 2”,(([Second Semester attendance]/[Semester2 total])*100))

All these virtual columns show the correct answer in a form after entering input data in prior fields,
however the problem is upon saving the record, the percentage attendance column becomes 0.00 always. how do i resolve this?

This is a common problem.

When two Number values are used in a calculation, the result will also be a Number. Number values are integers and have no fractional component (no decimal point), so when one Number is divided by another Number, any fraction in the result is lost. For instance, 5 / 2 gives 2, not 2.5.

If the two values are mixed, one Number and one Decimal, the result will be Decimal.

To avoid the problem, convert one or both of the Number values to Decimal where needed.

To convert a Number to a Decimal, either wrap the Number value with the DECIMAL() function, or multiply the Number value by 1.0. For instance, DECIMAL(5) / (2 * 1.0) (which illustrates both conversion methods) correctly gives 2.5.

In your case:

(([First Semester attendance]/[Semester1 total])*100)

could be changed to:

((DECIMAL([First Semester attendance])/[Semester1 total])*100)
1 Like

Thank you. Well understood.

1 Like

thank you! i thought i was going crazy!

1 Like