Forum Discussion
Cumulative/Running Total
Hello,
I'm having an issue with creating a Cumulative/Running Total. Currently I have the following:
- StartFTE_TO = sum('92_FOM_FY20'[Current FTE for TO])
- EndFTE_TO = SUM('92_FOM_FY20'[Current FTE for TO]) - sum('95_Terms_FY20'[Current FTE for TO]) + sum('98_Hires_FY20'[Current FTE for TO]) + sum('97_TransfersIn_FY20'[Current FTE for TO]) - sum('97_TransfersOut_FY20'[Current FTE for TO]) + sum('96_PromotionsIn_FY20'[Current FTE for TO]) - sum('96_PromotionsOut_FY20'[Current FTE for TO]) - sum('92_FOM_FY20'[Inactive Seasonal])
- TermsFTE_TO = sum('95_Terms_FY20'[Current FTE for TO])
- Turnover = ([TermsFTE_TO] / (([StartFTE_TO]+[EndFTE_TO]) / 2))
- Cumulative = CALCULATE([Turnover], FILTER(ALLSELECTED('7_Date'), '7_Date'[Date] <= MAX('7_Date'[Date])))
Below I'm using "FiscalMonthLong" as a Filter in my report from a Date Table (Marked as Date Table). The Turnover column is calculating correctly but as you can see my Cumulative column is not adding correctly. Also the "Total" row does not seem to calculate correctly unless I change this to a waterfall chart which displays the correct total.
23 Replies
You can create one using Quick Measures
Or Refer
https://community.powerbi.com/t5/Desktop/Cumulative-Total/td-p/43115
https://community.powerbi.com/t5/Desktop/Cumulative-Sum-of-a-column/td-p/345387
https://www.c-sharpcorner.com/article/calculate-cumulativerunning-total-in-power-bi/
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on LinkedinHi amitchandak
Thanks for the fast responce!
I've tried using the Quick Measure and seems that does not work I also tried YTD quick measure. see below screenshot
Hi jake223 ,
the problem lies in your matrix visual. Once you pull a column that indicates the year into the rows section, the values should turn out fine. As there is probably more than one June in your calendar table, it is not clear which year is meant - and therefore the order cannot be determined.
Hi ImkeF
I updated my date table From Start Date: 6/1/2016 To: 6/1/2019 the start of our fiscal year (June in the Image). and it still seems it only calculating the current month. But I do a think it might have to do with using Fiscal year just not sure how/what/why.
Thanks for all your Help!
Seem like it is doing Avg. Can you share the formula.
