Actual vs target variance and attainment
Show how far actual sales are above or below target, as a percentage of target.
Sales vs Target % =
VAR ActualSales = [Total Sales]
VAR TargetSales = [Total Target]
RETURN
DIVIDE(ActualSales - TargetSales, TargetSales)How it works
The measure compares the two numbers and divides the difference by the target. A negative value means below target. For attainment (95% of target) use DIVIDE(ActualSales, TargetSales) instead.
Both measures must be evaluated at the same level of detail, so the targets should be stored (or allocated) at the grain you show: month, region, product.
What your model needs
- A [Total Target] measure, for example Total Target = SUM(Targets[Target]). - The Targets table related to the same Date table and dimensions as Sales. - Total Sales = SUM(Sales[Amount]) (called [Total Sales]).
Small example
Input data
Total Sales = 95,000
Total Target = 100,000Expected result
Sales vs Target % = -5.0%
Attainment = 95.0%Common mistakes and limitations
- Monthly targets related to a daily date table are repeated on every day of the month by default, which inflates totals. Relate targets to the first day of the month or use a month key. - Targets that are not related to the same dimensions return the grand target on every row.