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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
1125NEWBIUSER
Helper I
Helper I

Sum columns not working

Hi,

I have got the hardest part of my DAX formulas working to get my running totals, now I want to add the 4 calculated fields I created together. I did a sumx formula, in picture below but it is not totalling correctly. If you manually add, you should have -149 but I am getting the value of 111 from the field. What did I do wrong here?

1125NEWBIUSER_0-1662863727809.png

Second question, I have running totals and I want to keep this life to date, independent of date filters. Meaning if I just grabbed todays date in filter, I want the totals to remain the same. But currently, it only calculates based on the dates I am pulling into table. So i cannot just see the RT (Running Total) Orders at a point in time. Is this possible? The formula for one below.

RT Orders =
Var LastSaleDate = Calculate( LastDate( 'orders/wip'[Order date]),ALL('Orders/WIP'[Quantity]))

RETURN
IF( SELECTEDVALUE( 'orders/wip'[Order date]) > LastSaleDate, BLANK(),
    -CALCULATE( sum('Orders/WIP'[Quantity]) ,
        FILTER( ALLSELECTED( 'Orders/WIP'),
            'orders/wip'[Order date] <= MAX ('orders/wip'[Order date]) && 'Orders/WIP'[Invoiced] <>"Return"
    && 'Orders/WIP'[SaleTransType] = "Standard")))
Thanks! Chris
1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

@1125NEWBIUSER If you want to add four fields together, just add them together, no need for a DAX function:

[Measure] + [Measure 1] + [Measure 2] + [Measure 3]

Same thing for columns.

 

I feel like you are overcomplicating your running total. I just posted a Better Running Total that demonstrates a super simple approach to this. https://community.powerbi.com/t5/Quick-Measures-Gallery/Better-Running-Total/td-p/2755666

 

Would need sample data and expected output to go further.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Community Champion
Community Champion

@1125NEWBIUSER If you want to add four fields together, just add them together, no need for a DAX function:

[Measure] + [Measure 1] + [Measure 2] + [Measure 3]

Same thing for columns.

 

I feel like you are overcomplicating your running total. I just posted a Better Running Total that demonstrates a super simple approach to this. https://community.powerbi.com/t5/Quick-Measures-Gallery/Better-Running-Total/td-p/2755666

 

Would need sample data and expected output to go further.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler for the first, that did the trick, thank you very much.

For the second item, that worked as well. I added my additional features to VAR __Table and it matches the other formula I used. Is the below enough for the data set example? Below is Table 1 (Left), I have the RT versus the orders per day. Table 2 (Right), I have same thing but filtered to just see this month. The running total adjusts to filtered date of what I want to show in chart versus I want the formula to be static so I can see the left results but at a period of time.

1125NEWBIUSER_0-1662900790230.png

Thanks, Chris

 

@1125NEWBIUSER Really need the source data as text in a table so that it can be copied and pasted into an Enter Data query. I don't like to do that much typing.

 

First check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@Greg_DecklerI believe I have added the based data/table as needed now. Also show examples of what I am currently getting versus what I hope to achieve.

 

What I have:

Order DateQuantity
3/1/2019150
12/1/2020500
5/1/2021100
9/1/2022450

 

What I want is a running total, current formula used:

Better RT Orders =
    VAR __Date = MAX('Orders/WIP'[Order date])
    VAR __Table = FILTER(ALLSELECTED('Orders/WIP'),[Order date] <= __Date && [Invoiced] <> "Return" && [SaleTransType] = "Standard")
RETURN
    (- SUMX(__Table,[Quantity]))
 
What I get:
Order DateQuantity
3/1/2019150
12/1/2020650
5/1/2021750
9/1/20221200

My current formula is doing this. But it is dynamic. So when I then put into a visual, lets say I only want to see this month, I currently see:

Order DateQuantity
9/1/2022450

Because the visual is filtered by date, the formula is updating to only show me running total for this month. What i want to see is:

Order DateQuantity
9/1/20221200

Is it possible to update my DAX so it calculates based on the total tables, not just on the visual filters that currently are driving these totals. I want to have a life to date running total that I can plug in to see at any one point in time.

 

Thanks, Chris

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.