SYMPTOM:
In the freeform SQL report below, the user has the metrics Cost, Revenue, and cost/revenue. Without making the cost/revenue metric a smart metric, the total will be the grand total instead of weighted average, as shown in the image below:
With smart metric enabled for the cost/revenue metric, the total displays as shown below:
CAUSE:
The smart metric feature is for compound metrics, and therefore is irrelevant to freeform SQL reports. By design, freeform SQL reports cannot contain compound metrics.
WORKAROUND:
Delete the cost/revenue metric and create a derived metric that divides the Cost metric by the Revenue metric. Then, change the evaluation order.
To change the evaluation order, navigate to the Data menu -> Report Data Options -> Calculations -> Evaluation Order. Change the order so the derived metric can be calculated first, as shown below:
