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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors