I need to calculate slab based commission in Power BI. Below is a sample of the commission table.
The percentage commission only applies to the amount that is considered in that category. So for a sale of $35000 the commission would be calculated by:
$25000 * 0,0228 = $570
$10000 * 0,0220 = $220
Total commission = $790
Seems simple, but can't figure out how to calculate this using a DAX formula. Any suggestions?
Category | Lower | upper | commission |
---|---|---|---|
0-25000 | 0 | 25000 | 2,28 |
25000,01-100000 | 25000,01 | 100000 | 2,20 |
10000,01-200000 | 200000,01 | 200000 | 2,32 |