Forum Discussion
Running Total
I am trying to calculate a running total for a production line based on the Machine name, there is no date value. I am using a Direct Query to SQL database. I want to add an exta column for running total of FNV, is this possible?
Eyy, I think I got it. Try this:
RunningTotal2 = MAXX(Table1, SUMX( FILTER( SUMMARIZE(CALCULATETABLE(Table1, ALLEXCEPT(Table1, Table1[Building]), ALLSELECTED(Table1[Building])), Table1[MachineName], "NetVal", SUM(Table1[Fabrication Net Value])), [NetVal] >= SUMX(FILTER(CALCULATETABLE(Table1, ALLEXCEPT(Table1, Table1[Building]), ALLSELECTED(Table1[Building])), Table1[MachineName]= EARLIER(Table1[MachineName], 2)), Table1[Fabrication Net Value])) , [NetVal]))
Hopefully you can see how that would scale out to other columns if you had multiple filters.
30 Replies
Where would the running total start, and what would be the next value added to the total?
I would sort the list in descending order of Fabrication Net Value and it would start with the first row and continue adding each row. Here is the result I am looking for.
Machine Name Fabrication Net Value Running Total Rollpacks 16752 16752 Tightwinder 12390 29142 T8 ( 2 ) 10217 39359 T8 ( 4 ) 5821 45180 Cut off saw 5801 50981 T8 ( 3 ) 2509 53490 T8 (1 ) 2155 55645 Tarp saw 568 56213 C52 Pillow Machine 309 56522 Bun Roller Machine 229 56751 Chinese Carousel 124 56875 To achieve this you need to add some order number to you machines for DAX to identify how it should calculate running total. In your example i have added SrNo column and gave sequence values to this. Next I added a new column with below formula and it gave me desired results....
Another idea other than SrNo is to use the row number based on sorted column of your choice
RunningTotal = CALCULATE(SUM(Machine[Fabrication Net Value]),all(Machine),Machine[SrNo]<=EARLIER(Machine[SrNo]))
Below is the output
Thanks for your help, unfortunately I could not get any of these solutions to work. Please remember I am using Direct Query so calculated columns is not an option. I have however succeded in creating a measure to calculate the ranking for each machine so maybe I could use that in the calculation. I created this one with dates and although it works does not serve for my needs:
CumulativeFabricationNetValueDate = CALCULATE ( SUM (vwScalesDataFabricationNetValue[Fabrication Net Value]), FILTER(ALL(vwScalesDataFabricationNetValue[WeightGeneralDate]), vwScalesDataFabricationNetValue[WeightGeneralDate] <= MAX(vwScalesDataFabricationNetValue[WeightGeneralDate])))
I tried doing something similar with the ranking measure instead of date but as yet no luck.
Did you try the measure I included earlier, and if so what was the result?
Here is the measure I used and the results:
RunningTotal = MAXX(vwScalesDataFabricationNetValue, CALCULATE(SUM(vwScalesDataFabricationNetValue[Fabrication Net Value]), vwScalesDataFabricationNetValue[Fabrication Net Value] >= EARLIER(vwScalesDataFabricationNetValue[Fabrication Net Value]), ALL(vwScalesDataFabricationNetValue)))
Ranking MachineName Fabrication Net Value RunningTotal 1 Quarrata 21003 74934 2 Rollpacks 16752 87249 3 Tightwinder 12390 78010 4 T8 ( 2 ) 10217 87114 5 T8 ( 4 ) 5821 88776 6 Cut off saw 5801 89000 7 OEM VCC's 5133 88822 8 T8 ( 3 ) 2509 85088 9 T8 (1 ) 2155 76690 10 Tarp saw 1906 88949 11 Mattress 1435 79222 12 OEM CNC 1126 89020 13 T8 ( 5 ) 889 88625 14 Fillmatic 724 35792 15 Quality Samples 497 85254 16 C52 Pillow Machine 309 88977 17 Bun Roller Machine 229 62330 18 Chinese Carousel 124 78134
