cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Microsoft
Microsoft

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

8 REPLIES 8
New Member

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

New Member

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

 

 

Microsoft
Microsoft

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

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!

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.

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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors