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 |