Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dax_bee
Frequent Visitor

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.

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
SaraM
Frequent Visitor

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] )

1111.png
    )
)

SaraM
Frequent Visitor

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] )
    )
)

 

 

v-yuezhe-msft
Employee
Employee

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello everyone, I have to calculate cumulative sum for distinct Ben per day in order to obtain this

fraofempire_1-1641460273675.png

 

My table have these columns:

 

fraofempire_0-1641460202520.png

 

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!

Sunkari
Responsive Resident
Responsive Resident

@dax_bee: What's the logic of my_field.

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.

Sunkari
Responsive Resident
Responsive Resident

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.