Power BI DAX to split the value in to multiple ranges

[Anonymous]

I have the data as below.

Product_Code	Channel	Account_Id	Max_tier	Balance
2000-0100	Direct	102123625	5000	174589.26
2000-0100	Direct	102123625	10000	174589.26
2000-0100	Direct	102123625	20000	174589.26
2000-0100	Direct	102123625	50000	174589.26
2000-0100	Direct	102123625	99999999999.99	174589.26

I am trying to build a report in Power BI, where the Balance should be split into a range that should fit into MAX_TIER.So the output should be as below

Product_Code	Channel_Desc	Account_Id	Max_tier	Balance	Split_range
2000-0100	Direct	102123625	5000	174589.26	5000
2000-0100	Direct	102123625	10000	0	10000
2000-0100	Direct	102123625	20000	0	20000
2000-0100	Direct	102123625	50000	0	50000
2000-0100	Direct	102123625	99999999999.99	0	89589.26

Any thought would be much appreciated

[parry2k]

@Anonymous what is the logic to get balance to last row?

[Anonymous]

Hi Parry,

The balance is 174589.26.

So we split into the MAX_tier range,

then the 1st range is 5000. So the remaining balance is 174589.26-5000 = 169589.26.

2nd split max is 10000. So the remaining balance is  169589.26 - 10000 = 159589.26

3rd split max is 20000. So the remaining balance is  159589.26 - 20000 = 139589.26

4th split max is 50000. So the remaining balance is  139589.26 - 50000 = 89589.26

5th split max is 9999999999.99. So the remaining balance is  89589.26

Another Example

if the balance is only 3500. Then this will fit into the 1st range itself

if the balance is 8500. Then this will fit into the 1st and 2nd range 

[parry2k]

@Anonymous does this looks correct?

[Anonymous]

Hi Parry,

That is exactly what i am looking for.