Aggregate Divide?

[Alyssaxw]

Hi everyone,

First, thanks for the awesome work the community has done so far!

My company adopted Perspective in a recent project but find it challenging to get the correct aggregated computation result when using grouping. I'd appreciate some guidance on the proper approach.

Scenario:
I have daily financial data with revenue and spend columns for different time periods. I want to calculate margin ratios (revenue ÷ gross spend) at different aggregation levels based on the user need.
Example Data:
Date | Revenue | Gross Spend
2024-01-01 | 100 | 80
2024-01-02 | 200 | 150
2024-01-03 | 50 | 30

Goal:
When grouping by week/month, I need:
Weekly margin = (sum of all revenue in week) ÷ (sum of all gross spend in week)
NOT the average of daily margins

What I've tried:
Created New Column CalculatedMargin and set the Attributes to: sum("Revenue") / sum("Gross Spend")
Set Group By: Date (weekly/monthly grouping)
Added the expression column to view

The Issue:
Perspective requires me to select an aggregation method for the expression column (sum, avg, count, etc.), but my expression already contains the aggregation logic (Although I'm not sure if it's working as I intended). The available options don't seem to give me the raw calculated ratio per group.

Question:
What's the correct way to display computed ratios in grouped data?
Any help would be appreciated!

[Alyssaxw]

@texodus this might be a silly question, but I'd really appreciate your help. Thank you!

[LarryHu0217]

For the data in your example, use a weighted mean of the row-level ratio, with gross spend as the weight. An ordinary mean of daily ratios gives a different result.

On the current API (master at 6dbc6b8), assuming Period is your existing week/month bucket column:

const view = await table.view({
    group_by: ["Period"],
    expressions: { Margin: '"Revenue" / "Gross Spend"' },
    columns: ["Margin"],
    aggregates: { Margin: ["weighted mean", ["Gross Spend"]] },
});

For each group, this computes

sum((Revenue / GrossSpend) * GrossSpend) / sum(GrossSpend)
= sum(Revenue) / sum(GrossSpend)

Your three rows therefore give 350 / 260 ≈ 1.346153846, rather than the unweighted mean 1.416666667.

This assumes revenue and spend are present and finite, and spend is strictly positive, as in your sample. With missing values or zero spend, decide the accounting policy separately: a non-finite row ratio can be skipped by the aggregate, so the equivalence above is not automatic for those inputs.

Perspective expressions are row-local; aggregation is configured separately. Sources: expression semantics, weighted-mean semantics and argument syntax, and expression-column aggregate coverage.