How can I calculate the of completion

Question

From the above sample data, I need to calculate % complete for the order number 1.

Calculation for complete: (total number of points for status 'Delivered' / total number of points)*100

The result should be 57%

Can someone help me with the calculated field?

Answer 1

SUM(IIF([Status]="Delivered",[Point],NULL)) / SUM([Point])

I assume that your column named Order No. extends all the way down each row (i.e., each row should have a 1 as Order No. but the screenshot does not show that.

Also, the above only works if the status of "Delivered" is consistently cased, which it is not in the screenshot. If it is not consistently cased, wrap [Status] in an Upper function:

SUM(IIF(UPPER([Status])="DELIVERED",[Point],NULL)) / SUM([Point])