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!