Forum Discussion
Running Total?
Hello,
I think I need help with calculating a running total, though I'm not sure. Apologies if this has been answered elsewhere.
I took some time to create an example file.
In the example file, I have two excel tables loaded into Power Query as connection only, and loaded to data model. I have not created any relationships or measures.
The first table shows employees and their FTE per position.
The second table is a list of dates incremented by 1 day.
I created a dummy end goal sheet (3rd tab). I'd like the user to be able to filter by employee and see a line chart of the running total(?) of FTE. Yes I'm using Excel... I hope it's clear what I'm trying to do!
I'm more of a power query guy than a DAX guy, and I think I need a DAX measure to achieve what I want...
Any help would be appreciated !!
- Kurt
Hi kbarber ,
I create a measure using SUMX function. You could reference it to have a try.
Measure = SUMX ( FILTER ( FTE, FTE[Pos Start] <= MAX ( 'Calendar'[Date] ) && ( FTE[Pos End] >= MAX ( 'Calendar'[Date] ) || ISBLANK ( FTE[Pos End] ) ) ), FTE[FTE] )Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi kbarber ,
I create a measure using SUMX function. You could reference it to have a try.
Measure = SUMX ( FILTER ( FTE, FTE[Pos Start] <= MAX ( 'Calendar'[Date] ) && ( FTE[Pos End] >= MAX ( 'Calendar'[Date] ) || ISBLANK ( FTE[Pos End] ) ) ), FTE[FTE] )Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-xuding-msft - that worked!!!
thank you so much for taking the time to respond; made my day!
In my limited understanding of DAX/Modeling, I thought I'd first have to create table relationships between the tables. I'm a bit surprised that no relationships are necessary...
thanks again,
- Kurt
We are having a peculiar situation where we need to calculating cumulative running totals in fly reducing a certain percentage value(.98). The new row should take the previous calculated value to cumulation.
Below is the sample data set. Here you see 5th row is cumulation till 4th row multiplied by .98, where 6th row should take the result of previous calculation that is 9.8 for cumulation and not 10.Please let me know your thoughts on this which is our last resort 😊
Rep Month/Year Measure Cumulative result Calculation
Rep1 2017001 0 0
Rep1 2017002 0 0
Rep1 2017003 0 0
Rep1 2017004 10 9.8 (0+10)*.98 = 9.8
Rep1 2017005 10 19.404 (9.8 + 10)* .98
Rep1 2017006 0 19.01 (19.04+0)*.98
Rep1 2017007 10 28.43 (19.01 + 10)*.98
Rep1 2017008 20 47.46 (28.43+20) * .98
Rep1 2017009 6 52.39 (47.46+6)*.98
Rep1 2017010 30
Rep1 2017011 0
Rep1 2017012 0hello Anonymous - since your use case is different, i'd recommend posting as a new question. Also, i find if i create a dummy file and link to the post it's more likely to get a response...
- Kurt
I would start with the Running Total Quick Measure built into Power BI Desktop. Even if you are using Excel, you can fire up Power BI Desktop and check out the DAX code. Failing that, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008
