Commission Report Calculations

Hello, I am trying to build a commission report in Power BI. Our commissions payout is divided so a sales rep get 60% of their commission right after the sale and the remaining 40% 90 days after....

Need Help

v-alq-msft

6 years ago

Hi, ehealthpartners

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is no relationship between two tables. You may create calculated columns and measures as below.

Calculated Column:
Year-Month = FORMAT('Calendar'[Date],"yyyy-mm")
Year-Month current = FORMAT('Table'[Date],"yyyy-mm")
Year-Month 90 days later = FORMAT('Table'[40% 90 days after sale date],"yyyy-mm")

Measure:
Sales = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[SaleRep],
    "Sales",
    var _salerep = [SaleRep]
    return
    CALCULATE(
        SUM('Table'[SalesTotal]),
        FILTER(
            ALL('Table'),
            'Table'[Year-Month current] in DISTINCT('Calendar'[Year-Month])&&
            'Table'[SaleRep] = _salerep
        )
    )
)
return
SUMX(
    tab,
    [Sales]
)

Commssion = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[SaleRep],
    "Commission",
    var _salerep = [SaleRep]
    return
    CALCULATE(
        SUM('Table'[Commission]),
        FILTER(
            ALL('Table'),
            'Table'[Year-Month current] in DISTINCT('Calendar'[Year-Month])&&
            'Table'[SaleRep] = _salerep
        )
    )
)
return
SUMX(
    tab,
    [Commission]
)

1st Payment 60% = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[SaleRep],
    "0.6 Sales",
    var _salerep = [SaleRep]
    return
    CALCULATE(
        SUM('Table'[0.6]),
        FILTER(
            ALL('Table'),
            'Table'[Year-Month current] in DISTINCT('Calendar'[Year-Month])&&
            'Table'[SaleRep] = _salerep
        )
    )
)
return
SUMX(
    tab,
    [0.6 Sales]
)

2nd Payment 40% 90 days prior = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[SaleRep],
    "0.4 Sales",
    var _salerep = [SaleRep]
    return
    CALCULATE(
        SUM('Table'[0.4]),
        FILTER(
            ALL('Table'),
            'Table'[Year-Month 90 days later] in DISTINCT('Calendar'[Year-Month])&&
            'Table'[SaleRep] = _salerep
        )
    )
)
var _result = 
SUMX(
    tab,
    [0.4 Sales]
)
return
IF(
    ISBLANK(_result),
    0,
    _result
)

Total Commission Paid = [1st Payment 60%]+[2nd Payment 40% 90 days prior]

Finally you may use the 'Year-Month' column from 'Calendar' table to filter the result.