cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cpmb
Frequent Visitor

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:

DateGroupIDCapacityConsumptionAmount Left
1/1/2019A11082
1/1/2019A215815
1/1/2019A315815
1/2/2019A11090
1/2/2019A21594
1/2/2019A315915
1/3/2019A110100
1/3/2019A215100
1/3/2019A315101
4 REPLIES 4
kentyler
Solution Sage
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 capacitiesfact_table.png

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.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


cpmb
Frequent Visitor

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. 

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





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


cpmb
Frequent Visitor

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors