Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-yiruan-msft
Community Support
Community Support

Hi @Anonymous ,

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.
Anonymous
Not applicable

@v-yiruan-msft @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])))
Anonymous
Not applicable

@v-yiruan-msft  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
Super User

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

Anonymous
Not applicable

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.