cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
manisharma01
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
yingyinr
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

 

 

amitchandak
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.