Help with Creating a Power BI Dashboard for Sales Commissions with Cumulative Tiers

HI Chateauunoirr ,

To create a Sales Commission Dashboard with cumulative tiers in Power BI, follow these steps:

 

1. Data Model Setup
Your data model should have:

Sales Table (Opportunities Table)

• OpportunityID
• Amount (€)
• ClosingDate
• Salesperson

Commission Tiers Table

• Tier Min (€)
• Tier Max (€)
• Percentage (%)

 

2. Calculation Logic
Since the commission tiers are cumulative, you need to:

• Sort opportunities by closing date to apply commissions sequentially.
• Calculate cumulative sales for each salesperson.
• Determine the applicable tier(s) for each sale.

Apply tier-based commissions:

If the cumulative sales exceed a tier, split commission calculations between tiers.

DAX Measures
Cumulative Sales Calculation
To get the total contracted amount up to the current opportunity:

Cumulative Sales =
CALCULATE(
SUM('Sales'[Amount (€)]),
FILTER(
ALLSELECTED('Sales'),
'Sales'[ClosingDate] <= MAX('Sales'[ClosingDate])
)
)

Tier Commission Calculation
To compute the commission based on cumulative tiers, we need:

1. Identify the previous tier cumulative amount.
2. Check how much of the new sale falls into a higher tier.

DAX

Commission Calculation =
VAR CurrentAmount = SUM('Sales'[Amount (€)])
VAR CumulativeSales = [Cumulative Sales]

VAR PrevTier =
MAXX(
FILTER(
'Commission Tiers',
'Commission Tiers'[Tier Max (€)] < CumulativeSales
),
'Commission Tiers'[Tier Max (€)]
)

VAR CurrentTier =
MINX(
FILTER(
'Commission Tiers',
'Commission Tiers'[Tier Min (€)] <= CumulativeSales &&
'Commission Tiers'[Tier Max (€)] >= CumulativeSales
),
'Commission Tiers'[Tier Max (€)]
)

VAR PrevTierRate =
MAXX(
FILTER(
'Commission Tiers',
'Commission Tiers'[Tier Max (€)] = PrevTier
),
'Commission Tiers'[Percentage (%)]
)

VAR CurrentTierRate =
MAXX(
FILTER(
'Commission Tiers',
'Commission Tiers'[Tier Max (€)] = CurrentTier
),
'Commission Tiers'[Percentage (%)]
)

VAR AmountInPrevTier = MIN(CurrentAmount, PrevTier - (CumulativeSales - CurrentAmount))
VAR AmountInCurrentTier = CurrentAmount - AmountInPrevTier

VAR Commission =
(AmountInPrevTier * PrevTierRate) + (AmountInCurrentTier * CurrentTierRate)

RETURN
Commission

3. Dashboard Visualization
Key Elements
KPIs

• Total Sales
• Total Commissions Earned
• Number of Deals Closed

Tables

• Sales Transactions with Commission Breakdown
• Tier Thresholds with Applicable Percentage

Charts

• Cumulative Sales Over Time (Line Chart)
• Commission Earned by Salesperson (Bar Chart)
• Deals Closed by Month (Column Chart)

 

Additional Enhancements

• Add a slicer for Year, Salesperson, and Month.
• Use conditional formatting to highlight commissions that exceed a threshold.
• Export reports as PowerPoint or PDF for sales teams.

Please mark this post as solution if it helps you. Appreciate Kudos.