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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
FawadRehman
Helper I
Helper I

Running Total - DAX

I need help. I am looking for same solution http://community.powerbi.com/t5/Desktop/DAX-Running-total-by-another-column/m-p/10512#M2189 but somehow its not working for me. 

running total.JPG

 

RunningTotal =
CALCULATE (
SUM ( TabelForecast[Forcast] );
FILTER ( ALL ( TabelForecast ); TabelForecast[MonthYear] <= MAX ( TabelForecast[MonthYear] ) );
VALUES ( TabelForecast[M#] )
)

 

 

I have tried many formulas in power BI but its not giving me running total but total for all the rows Smiley Sad Any suggestions?

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@FawadRehman This should work... Let me know.

 

Running Total COLUMN =
CALCULATE (
    SUM ( TabelForecast[Forecast] ),
    ALLEXCEPT ( TabelForecast, TabelForecast[M#] ),
    TabelForecast[MonthYear] <= EARLIER ( TabelForecast[MonthYear] )
)

View solution in original post

14 REPLIES 14
Sean
Community Champion
Community Champion

@FawadRehman This formula is for a MEASURE not a Calculated Column

 

see post you are referencing => it is used in a PIVOT TABLE

 

EDIT:

2016-06-08 - Running Total - DAX.png

@Sean Thank you, this is great. But at the same time, i have this in table,then i make a outer left join this table to another table to insert my running my Running total column values. I am very new in Power BI so please excuse my stupid logic or questions.  Thanks

Sean
Community Champion
Community Champion

Can you provide a sample of the desired final result? Are you doing a summary table?

 

 

Project NameM#MonthYear Forecast  RunningTotal 
KR0779930-AADecember, 2015 $              600,000.00 $                             600,000.00
KR0779930-AAJanuary, 2016 $              865,000.00 $                         1,465,000.00
KR0779930-AAMarch, 2016 $           1,500,000.00 $                         2,965,000.00
KR0779930-AAAugust, 2016 $              500,000.00 $                         4,065,000.00
KR10779930-AAJuly, 2016 $              600,000.00 $                         3,565,000.00
KR107799DDFebruary, 2016 $           1,100,000.00 $                         1,100,000.00
KR107799DDApril, 2016 $                 10,000.00 $                         1,110,000.00
KR107799DDMay, 2016 $           2,500,000.00 $                         3,610,000.00
MPDO6000December, 2015 $           1,000,000.00 $                         1,000,000.00
MPDO6000January, 2016 $           3,000,000.00 $                         4,000,000.00
MPDO6000February, 2016 $           3,100,000.00 $                         7,100,000.00
JRR16ID80004December, 2015 $           2,300,000.00 $                         2,300,000.00
JRR16ID80004January, 2016 $           2,500,000.00 $                         4,800,000.00
JRR16ID80004February, 2016 $           3,300,000.00 $                         8,100,000.00
JRR16ID80004March, 2016 $           4,000,000.00 $                       12,100,000.00
MPRAM15IE10101October, 2015 $              500,000.00 $                             500,000.00

 

Here is the table. No its to summary table, just normal table and i am add one new calculated column "Running table". Btw, projects could go 5 years or more so Max date funtion is necessary. 

Sean
Community Champion
Community Champion

@FawadRehman Why don't you want to do the Running Total in the new Merged Table?

@Sean any update sir?

Sean
Community Champion
Community Champion

@FawadRehman This should work... Let me know.

 

Running Total COLUMN =
CALCULATE (
    SUM ( TabelForecast[Forecast] ),
    ALLEXCEPT ( TabelForecast, TabelForecast[M#] ),
    TabelForecast[MonthYear] <= EARLIER ( TabelForecast[MonthYear] )
)

Can I calculate a running total without a date field.  I have a list of Machine Names and production total for each machine.  With the production total showing in descending order I want to add a running total from highest to lowest.  Data is from SQL database with a direct link.  Please let me know if this is possible.  The idea is when I drill down or filter the calculation of the running total will recalculate based on data showing.

Working like a charm. Thank You. btw do you know line break in text box? 

Sean
Community Champion
Community Champion

I know some people use the Long Text Viewer Custom Visual instead...

https://app.powerbi.com/visuals/show/LongTextViewer1453740445633

 

Here is my lookup column code

 

Full Contact = LOOKUPVALUE(' Contacts'[Manager Name],' Contacts'[Id],'Customer Contact'[ ContactId])
& "i want line break here " & LOOKUPVALUE(' Contacts'[Email],' Contacts'[Id],'Customer Contact'[ ContactId])
& "i want line break here " & LOOKUPVALUE(' Contacts'[Telephone],' Contacts'[Id],'Customer Contact'[ ContactId])

 

 

output

John Doe

JD@powerBI.com

123-345-5677

 

Thanks

@Sean other table is coming from other source which has almost same columns and i want to join with M# ,MonthYear & Project Name.  

kcantor
Community Champion
Community Champion

@FawadRehman I believe you have to create a table to make that work. Beyond my meger skill set but @Sean can help you with this.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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