Hello!
Have a bit of an interesting one I think. I work in commercial real estate and I am trying to show by month what their monthly revenue falls under in their respective commissions.
I have a monthly GL that I get revenue from and I also have the running total set up so I can know cumulatively where they sit in their commission tranches. What I then want to do is show of that month how are they progressing through their tranche. Example dataset below:
| Tranche Number | Tranche Revenue | Tranche Rate | Cumulative Revenue | Monthly Revenue |
| 1 | 0 | 50% | $983,849.41 | $564,594.14 |
| 2 | $500,000 | 55% | $983,849.41 | $564,594.14 |
| 3 | $750,000 | 60% | $983,849.41 | $564,594.14 |
| 4 | $1,200,000 | 65% | $983,849.41 | $564,594.14 |
Essentially it works like a tax bracket, so how I want the data to show is this:
| Tranche Revenue | Tranche Rate | Monthly Revenue Breakdown |
0 | 50% | $80,744.59 |
$500,000 | 55% | $250,000 |
| $750,000 | 60% | $233,849.55 |
| $1,200,000 | 65% | $0 |
The total being that monthly revenue of $564,594.14
The goal of this is to show what monthly revenue we have in each tranche rate, but it is very dynamic and millions of rows with difference tranche structures.
My logic on how I got to the above table is this:
The cumulative revenue is $983,849.41. I subtracted the monthly revenue of $564,594.14 to get to what they have cumulatively done in past months and that was: $419,255.27.
So that means that $419,255.27 has taken up the $500K threshold, so now there should be $80,744.73 slotted in the $500K threshold because once they eclipse that amount by year they start earning at a higher rate of 55%.
We now have $483,849.41 left to go through.
The threshold is $500 - $750K, this means it will just be completed that month too, so simply you just know it has to be $250K.
Then the remainder is put in the $750K threshold because they haven't eclipsed $1.2M.
The tricky part is I am just unsure how to make this dynamic, like I said there can be a multitude of tranches, some are 1 line, some are six with varying levels of revenue and I get the revenue by month not by day.
I have some helper tables as well that show a 1 if it is the first time they have surpassed the revenue level, this helps because I can tell power bi so no longer apply future revenue to that specific level.
Anyways, let me know your thoughts, I know it is possible as this is mainly just very alegebra based.
Thank you!!
