Forum Discussion
Apply difference to multiple IDs within a group in DAX
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 |
4 Replies
- kentyler
Solution Sage
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.
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...
