Forum Discussion
Create Running Total On A Calculated Measure
Hi.
I am trying to create a running total for a field that is defined as a Calulated Measure. Having read numerous posts I am clear about the formula struture which needs to be implemented;
RunningTotal = CALCULATE(SUM(my_field),
FILTER(ALL(my_table),
my_table.date_field <= MAX(my_table.date_field)
)
)
The issue is regarding the SUM component. "my_field" is a very complex measure and the customer would like a running total of this field year on year. I know SUM cannot be used in conjunction with a measure, so can anyone help with how best to implement this requirement?
Thanks In Advance.
Hi dax_bee,
Create running total using the following DAX formula and check if you get desired result.
RunningTotal = SUMX(FILTER(ALLSELECTED('Calendar'[DateKey]),'Calendar'[DateKey]<=MAX('Calendar'[DateKey])),[my_field])If the above Dax doesn’t help in your scenario, please help to share sample data of your table and post expected result.
Thanks,
Lydia Zhang
10 Replies
Hi dax_bee,
Create running total using the following DAX formula and check if you get desired result.
RunningTotal = SUMX(FILTER(ALLSELECTED('Calendar'[DateKey]),'Calendar'[DateKey]<=MAX('Calendar'[DateKey])),[my_field])If the above Dax doesn’t help in your scenario, please help to share sample data of your table and post expected result.
Thanks,
Lydia Zhang- Many thanks for sharing this. Helped big time. I hope you wont mind answering one quick question? When I used this formula, the cumulative total continues to show constant value throughout the x axis even if there is zero value for the later groups. How can we stop running total from continuing all the way? Please advise. Thanks
Thank you for all your help.
v-yuezhe-msft - your solution worked a treat!
Hello everyone, I have to calculate cumulative sum for distinct Ben per day in order to obtain this
My table have these columns:
Hi.
The logic of my_field is very complicated and consists of references to a number of other columns. So ideally I do not want to replicate within the Running Total logic.
Hmm. As don't the exact logic of my_field, if possible try to push that logic at ETL(Power Query) level and use that column in sum function.
Hi,
I am using this formula in my tables for running totals on column **bleep** from coulmn 3 with dates in coulmn 2 but it doesn't seem to work. it bring the exact value as coulmn 3 and not totals. Is there anything that i need to change ?
CUMULATIVE = CALCULATE (
SUM ( Phasing[Column3] ),
FILTER (
ALL ( Phasing[Column3] ),
Phasing[Column2] <= MAX ( Phasing[Column2] )
)
)Hi there,
I am using this formula to run the total in my table but it doesnt work . it bring the exact same value from the column i am looking to sum.
Column 3 is monthly cost and column 2 is dates.
Do I need to chnage the formula???
CUMULATIVE = CALCULATE (
SUM ( Phasing[Column3] ),
FILTER (
ALL ( Phasing[Column3] ),
Phasing[Column2] <= MAX ( Phasing[Column2] )
)
)
