Common formula patterns

These patterns use supported formula syntax. Replace the example property and dimension names with names from your model.

Gross profit

Revenue - `Cost of Goods Sold`

if(Revenue <> 0, (Revenue - `Cost of Goods Sold`) / Revenue, 0)

Actual - Plan

if(Plan <> 0, (Actual - Plan) / Plan, 0)

Revenue - Revenue[[-1]]

At monthly grain, this compares the current month with the previous month.

sum(Revenue[Department: ANY])

if(sum(Revenue[Department: ANY]) <> 0, Revenue / sum(Revenue[Department: ANY]), 0)

Because the plain Revenue follows the current segment, this produces each department’s share when calculated by Department.

average(Revenue[Region: {"East", "West"}])

Headcount * `Average Salary`

Assign the formula to Forecast when Actuals should continue to use imported values.

if(Revenue > 100000, Revenue * 0.05, Revenue * 0.03)

date(year(Date.Month), 12, 31)

Before saving a pattern, confirm its target property, scope, Date grain, and scenario.