Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |