Cumulative price calculations in PowerBI

I have a dataset of price points per different names and I want to create in PowerBi the cumulative total price for each row for each name.

I 've tried creating a measure that calculates the sum of "level price" but I can't make it work for each name separately.

This is my table:

id	name	lower	upper	unit
0	AA1	0	0	$ -
1	AA1	0	10,000	$ 5
2	AA1	10,000	50,000	$ 4
3	AA1	50,000	10,000	$ 3
0	AB1	0	0	$ -
1	AB1	0	100,000	$ 10
2	AB1	100,000	250,000	$ 9
3	AB1	250,000	500,000	$ 8
0	AC1	0	0	$ -
1	AC1	0	10,000	$ 6
2	AC1	10,000	50,000	$ 5
3	AC1	50,000	100,000	$ 4

For example, if we want to calculate total price for each upper level of "AA1", we would have:

for id=0 price would be zero (0*0)
for id=1 price would be (10.000-0)*5 = 50.000$
For id=2 price would be 50.000(price of id=1) + (50.000-10.000)4 = 50.000+40.0004=210.000

and so on until we reach id=3 and then we start over because we have a new name(each name might have 1,5,6,10 ids or more)

In order to achieve the above results I created in Power BI two helper columns when I imported the dataset:

size -> it is the difference of upper - lower quantities
level_price -> is each name's price per id

Any ideas what would be the simplest way to write this? Once this achieved I would be able to filter in the dashboard view "AA1" And see a table with all levels of AA1 only and final price per upper limit.

Expected Outcome:

id	name	lower	upper	unit	size (upper-lower)	level_price (unit*size)	total_price (this is the desired outcome fromBI)
0	AA1	0	0	$ -	0	0	0
1	AA1	0	10,000	$ 5	10,000	$50,000	$50,000
2	AA1	10,000	50,000	$ 4	40,000	$160,000	$210,000
3	AA1	50,000	100,000	$ 3	50,000	$150,000	$360,000
0	AB1	0	0	$ -	0	0	0
1	AB1	0	100,000	$ 10	100,000	$1,000,000	$1,000,000
2	AB1	100,000	250,000	$ 9	150,000	$1,350,000	$2,350,000
3	AB1	250,000	500,000	$ 8	250,000	$2,000,000	$4,350,000
0	AC1	0	0	$ -	0	0	0
1	AC1	0	10,000	$ 6	10,000	$60,000	$60,000
2	AC1	10,000	50,000	$ 5	40,000	$200,000	$260,000
3	AC1	50,000	100,000	$ 4	50,000	$200,000	$460,000

Next steps would be to be able to calculate the price for any requested quantity, but this is a long shot since I am struggling with simple steps yet!

Solution

Try a calculated column like this one:

total_price = 
  var curId = [id]
  var curName = [name]
  RETURN CALCULATE(SUM([level_price]), ALL('YourTable'), 'YourTable'[name] = curName && 'YourTable'[id] <= curId)

Additional - based on your ask in the comments:

total_price = 
  var curId = [id]
  var curName = [name]

  var result = SWITCH([type],
    "cumulative",
       CALCULATE(SUM([level_price]), ALL('YourTable'), 'YourTable'[name] = curName && 'YourTable'[id] <= curId),

    "standard",
       [size] * [unit],

    "tier",
       [upper size] * [unit]
   )

  RETURN result