How to calculate discount contribution in DAX: step by step
This tutorial shows how to calculate discount contribution in DAX: that is, how much of the profit (or margin) from sales is explained by applied discounts. It is useful to assess the impact of discounts on profitability and to create reports that isolate price vs. volume effect.
Prerequisites
- Power BI Desktop or Analysis Services with a tabular model
- Model with at least the tables: Sales (Sales[Quantity], Sales[UnitPrice], Sales[DiscountAmount]) and Product/Date if possible
- Basic DAX knowledge: measures, CALCULATE, FILTER
Step 1: Understand what "discount contribution" is
Before writing DAX, define what we want to measure. Here we will calculate: Total discount amount (DiscountAmount) and the margin difference if the price had been without discount. The discount contribution will be the reduction in margin caused by discounts, usually expressed in absolute value and as a percentage of the profit without discount.
Step 2: Create base measures — Revenue, Cost and Discount
Start by creating simple measures that serve as the basis for more complex measures.
Revenue = SUM(Sales[Quantity] * Sales[UnitPrice])
Discount = SUM(Sales[DiscountAmount])
Cost = SUM(Sales[Quantity] * Sales[UnitCost])
Notes: if there is no UnitCost, adapt to have CostTotal in the Sales table. Ensure DiscountAmount is the monetary value of the discount, not the percentage.
Step 3: Measure for actual margin and margin without discount
Actual margin is profit after discounts: Revenue - Discount - Cost. To simulate a scenario without discounts, we assume the price without discount is UnitPrice + (DiscountAmount / Quantity).
GrossMarginReal = [Revenue] - [Discount] - [Cost]
Revenue_NoDiscount = SUMX(Sales, Sales[Quantity] * (Sales[UnitPrice] + DIVIDE(Sales[DiscountAmount], Sales[Quantity])))
GrossMargin_NoDiscount = [Revenue_NoDiscount] - [Cost]
Explanation: Revenue_NoDiscount recalculates revenue by adding the per-unit discount back to UnitPrice. DIVIDE is used to avoid division by zero.
Step 4: Calculate the absolute discount contribution
The absolute discount contribution is the difference between the margin without discount and the actual margin: how much margin was lost by applying discounts.
Discount_Contribution_Value = [GrossMargin_NoDiscount] - [GrossMarginReal]
If the value is positive, it means discounts reduced the margin. If it is zero, there was no impact.
Step 5: Calculate the contribution as a percentage
It is useful to have the percentage that shows the relative reduction of margin caused by discounts. To avoid divisions by zero we use DIVIDE and handle scenarios where the margin without discount is zero or negative.
Discount_Contribution_Pct =
DIVIDE(
[Discount_Contribution_Value],
ABS([GrossMargin_NoDiscount]),
0
)
We use ABS(...) to have a positive denominator; if GrossMargin_NoDiscount is zero, the result is 0 according to the third argument of DIVIDE.
Step 6: Filter by product, category or period
For analyses by product or by period, the previous measures work in context. If you want to force a filter (for example, only products with discounts), use CALCULATE with FILTER.
Discount_Contribution_Value_DiscountedOnly =
CALCULATE(
[Discount_Contribution_Value],
FILTER(ALL(Sales), Sales[DiscountAmount] > 0)
)
Note: ALL(Sales) removes the current context; adjust as needed (for example ALL(Product) to remove only the product filter).
Verify the result
Create a report in Power BI with a table that includes Product[ProductName], [Revenue], [Discount], [GrossMarginReal], [GrossMargin_NoDiscount], [Discount_Contribution_Value], [Discount_Contribution_Pct]. Check that:
- Totals make sense (Revenue = unit sum, Discount = sum of discounts)
- For sales without discount, Discount_Contribution_Value should be ~0
- For lines with high discounts, the percentage shows the relative reduction
- Test with date filters to confirm behavior by period
Conclusion
You now have measures to quantify how discounts affect margin, both in value and in percentage. Next steps: include scenarios with price elasticity (simulate impact on volume), or create conditional visualizations that alert for discounts with high impact. Tip: always verify that DiscountAmount is recorded per sales line and handle divisions by zero with DIVIDE.