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

Cumulative total by category and date

Hello,

 

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. 

Capture.PNG

 

 

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]<=MAX(WORK_ORDER[DESIRED_WANT_DATE]) &&
WORK_ORDER[DESIRED_WANT_DATE] >= MIN(WORK_ORDER[DESIRED_WANT_DATE])),
GROUPBY(REQUIREMENT,REQUIREMENT[PART_ID])
)

 

 

 

5 REPLIES 5
Community Support
Community Support

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.

Best Regards
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.

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@yingyinr @amitchandak 

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. 

manisharma01_0-1606408103345.png

test = CALCULATE(sumx(REQUIREMENT,REQUIREMENT[Balance]),filter( REQUIREMENT, REQUIREMENT[PART_ID] = EARLIER(REQUIREMENT[PART_ID]) && REQUIREMENT[WORK_ORDER.DESIRED_WANT_DATE] <=EARLIER(REQUIREMENT[WORK_ORDER.DESIRED_WANT_DATE])))

@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.

manisharma01_0-1606317933611.png

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_1-1606322523900.png

 

 

Super User IV
Super User IV

@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])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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.

RT = CALCULATE(
SUMX( REQUIREMENT,REQUIREMENT[quantity needed]),
FILTER ( ALLSELECTED( WORK_ORDER[DESIRED_WANT_DATE]),
WORK_ORDER[DESIRED_WANT_DATE]<=MAX(WORK_ORDER[DESIRED_WANT_DATE]) &&
WORK_ORDER[DESIRED_WANT_DATE] >= MIN(WORK_ORDER[DESIRED_WANT_DATE])),
GROUPBY(REQUIREMENT,REQUIREMENT[PART_ID])
 
)
 
what is happening is it is taking cumulative sum by date. for example : multiple work order on one date. It is showing me the sum of all the work orders due for that that. and then sum of all the work order for another date.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors