I need some help in calculating running total.
Available quantity , quantity needed are measures I created.
Parent product and product are coming from two separate tables. Date is coming from the third table. There is a slicer to select the dates.
I need help in creating a measure for calculating running total by product within the selected dates.
Thank you so much in advance.
The calculated column that I am creating is something like this. I am getting the same values as quantity needed column.
RT = CALCULATE(
SUMX( REQUIREMENT,REQUIREMENT[quantity needed]),
FILTER ( ALLSELECTED( WORK_ORDER[DESIRED_WANT_DATE]),
WORK_ORDER[DESIRED_WANT_DATE] >= MIN(WORK_ORDER[DESIRED_WANT_DATE])),
Hi @manisharma01 ,
Could you please provide some sample data (exclude sensitive data) in table REQUIREMENT and WORK_ORDER in order to make troubleshooting? Is there any relationship created among Date, REQUIREMENT and WORK_ORDER table? If yes, please also provide the related relationship fields. Besides this, please provide the formula of measure [Available Quantity] and [Quantity Needed]. It is better if you can provide a sample pbix file.Thank you.
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.
I have tried creating a new column in the requirement table balance and test. Now what is happening is if the work order want date is same. It is all being grouped together.
@yingyinr The schema looks something like this. Part 2 is a copy of the part table because I needed parts needed to make the bigger part.
Work Order and requirement table have a relationship with work order id.
Date is the work order date.
Available qty is coming directly from part2 table. created a simple measure for it using sumx.
balance is total qty needed - requirement[issued qty]. I am looking for cumulative sum of balance by part2[id]. I need to subtract that sum from available qty to see which work orders can be completed in selected dates.
For example: I need this part_id to complete these 8 work orders and I only have 48 in stock right now. If I can get a cumulative sum of balance by partid , I can subtract that value from qty on hand to see which work orders can be completed.
@manisharma01 , Option as new measures and new column (Running Total
new column = sumx(filter( Table, [product] =earlier([product]) && [Date] <=earlier([Date])), [Quantity needed])
new measure = sumx(filter( allselected(Table), [product] =max([product]) && [Date] <=max([Date])), [Quantity needed])
@amitchandak , date is coming from table 1, parent part is coming from table 1 and part is coming from table3. available quantity is coming from table 3 and quantity 3 is a measure based on columns from table 1
The calculated column that I am creating is something like this. I am getting the same values as quantity neededcolumn.
Check out new user group experience and if you are a leader please create your group
Click here to read more about the April 2021 Updates!
May the fourth be with you, join us online!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.