Apply difference to multiple IDs within a group in DAX

[cpmb]

I have a table with daily Consumption totals and a fixed Capacity amount per ID. I want the Consumption value to be subtracted from the "first" ID's Capacity until it is 0, and then subtract the remaining amount from the next ID within the group. Desired output is the Amount Left column below:

Date	Group	ID	Capacity	Consumption	Amount Left
1/1/2019	A	1	10	8	2
1/1/2019	A	2	15	8	15
1/1/2019	A	3	15	8	15
1/2/2019	A	1	10	9	0
1/2/2019	A	2	15	9	4
1/2/2019	A	3	15	9	15
1/3/2019	A	1	10	10	0
1/3/2019	A	2	15	10	0
1/3/2019	A	3	15	10	1

[kentyler]

Your first thought should probably be to change your data to a star schema, where you have a fact table that records events as they happen and a dimension table that describes capacities

the dimension table describes the batches you have to draw from. The fact table describes the events when different quantities are withdrawn.  So when you ask for a certain quantity you would need one measure to decide which batch 1,2 or 3 it should be drawn from.  Does the user decide the batch ? or is the logic just that when batch 1 is used up, switch to batch 2. If so you could just have the measure retun the batch number that the quantity for that row was going to be drawn from. You would have to think about what to do if you run out of all the batches.

[cpmb]

I created a calculated table from the fact table that records the Capacity, as this changes daily based on deliveries to each ID (i/e sometimes the ID will not always be at maximum Capacity). Not all days are available in the fact table, so I created one that contains a date value for every ID. Ideally the ID with the highest Capacity remaining would take in the remaining value between the first ID. Logically a statement like:

IF([ID1]-[Consumption] <=0, [ID2]-ABS([ID1]-[Consumption]),[ID1]-[Consumption])

would work, however I cannot figure out how to get this into Power BI. 

[kentyler]

any chance you can post a sample power bi file that i could look at...

[cpmb]

File is Attached Below.   I am looking to compute the running total of ID - running total Consumption and if that value is < 0, apply the leftover to the related ID within the Group. Either I would like to add a calculated column to the ID table, or create a measure in the matrix. Appreciate any ideas you would have.

Note: There are Consumption Rates for all Days. Also, the data in the file will not match the Excel table above. I just recreated my Query, calculated tables, and relationships in the sample PBI file to help visualize what I have already. 

link