Forum Discussion
Transaction fee calculation based on tiered pricing model
Hi all,
I'm looking for a way to calculate transaction fees based on tiered pricing model. This means that for each tier (see table 2 below), the tier rate is incrementally applied to the volume within the tier volume range.
I've added below an illustration of the calculation (row 3 in table 1 below). Within Excel, I used sumproduct formula, but I could not figure out an alternative in PowerBI. I have a slight preference for a solution in PowerQuery, but DAX calculation would obviously suit as well.
Your help would be very much appreciated.
Table 1: Transaction fee calculation
| March 2018 | April 2018 | May 2018 | |
| Transaction volume | 2.300.000 | 400.000 | 8.300.000 |
| Calculated fee | 47.499,94 | 10.499,98 | 141.999,93 |
| Calculated fee formula | =(100000-0)*0,03+(500000-100001)*0,025+(2000000-500001)*0,02+(2300000-2000001)*0,015 | =(100000-0)*0,03+(400000-100001)*0,025 | =(100000-0)*0,03+(500000-100001)*0,025+(2000000-500001)*0,02+(2300000-2000001)*0,015+(8300000-2000001)*0,015 |
Table 2: Tiered fee table
| Min | Max | Rate € |
| 0 | 100.000 | 0,030 |
| 100.001 | 500.000 | 0,025 |
| 500.001 | 2.000.000 | 0,020 |
| 2.000.001 | 5.000.000 | 0,015 |
| 5.000.001 | 10.000.000 | 0,008 |
| 10.000.001 | 20.000.000 | 0,005 |
| 20.000.001 | + | 0,003 |
Regards
Hi Anonymous
Here is the snapshot of the solution
Please download the Power BI file from here. Hope it helps
8 Replies
- ChandeepChhabra
Impactful Individual
Anonymous Can you also share how your other table looks, which has transaction volumes ?
Many thanks for your interest
i've highlighted the transaction volumes below in red. It's a simple table with a single date dimension in a column (below example is just an illustration and doesn't reflect the format of the table). These are then computed into the tiered rate table for each volume range. Let me know should you need further clarifications. I can try to add a link to a workbook should it be necessary.
March 2018
April 2018
May 2018
Transaction volume
2.300.000
400.000
8.300.000
Calculated fee
47.499,94
10.499,98
141.999,93
Calculated fee formula
=(100000-0)*0,03+(500000-100001)*0,025+(2000000-500001)*0,02+(2300000-2000001)*0,015
=(100000-0)*0,03+(400000-100001)*0,025
=(100000-0)*0,03+(500000-100001)*0,025+(2000000-500001)*0,02+(2300000-2000001)*0,015+(8300000-2000001)*0,015
- ChandeepChhabra
Hi Anonymous
Here is the snapshot of the solution
Please download the Power BI file from here. Hope it helps