Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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] )
)
)
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 @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
Hello everyone, I have to calculate cumulative sum for distinct Ben per day in order to obtain this
My table have these columns:
Thank you for all your help.
v-yuezhe-msft - your solution worked a treat!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |