cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DataNoobie
Helper I
Helper I

Easy in Excel but not in Dax Measure (running total)

Hi all, I need to replicate a calculation I have in Excel using a DAX measure. It's to get a running total. It's B3 - C3 + D2 (where D2 is the running total copied down the column). Can column D be calculated using a dax measure in PowerBI?

 

DataNoobie_0-1619556539532.png

 

I can calculate the running total of column B using:

 
running total measure of amount1 :=
VAR IndexID = SELECTEDVALUE( 'ID_Table'[ID], 0 )

RETURN
 
SUMX (
FILTER (
SUMMARIZE ( ALLSELECTED ( 'ID_Table' ), 'ID_Table'[ID],
"RunningTotal", [Amount1] ),
'ID_Table'[ID] <= IndexID),
[RunningTotal] )
 
7 REPLIES 7
DataNoobie
Helper I
Helper I

Thanks for looking @edhans. I know it's not straight forward because I'm having to build a virtual table to be able to do the calculations on the measures (I'm trying to perform calculations on things that don't exist). The values visible in the table visual on the canvas are results of report filtering and slicers from user input (what if parameters). 

 

I think I struck lucky with getting a running total to work on one measure using summarize. I did try to add ADDCOLUMNS but the running total wouldn't work when I did. 

 

I will need to find another way to do this and not use Power BI.

Understood. I am 100% confident this can be done in Power BI - but not the way the measures are built. It is next to impossible to know where to change the filters to get the desired results. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
DataNoobie
Helper I
Helper I

@edhans The above didn't work. Here is a link to the PBI file and the Excel file with the calculation that I'm trying to replicate. 

This isn't a simple question @DataNoobie - this is more of a project. Your amount 1 measure is referring to three measures. I only traced one of those measures through, but it goes two more levels deep, and for some reason one of them is this measure:

C/way width availability % =
AVERAGEX(
    'key measures',
    'Key Measures'[C/way width (meters) in use in TM]
)
    / (
        SUMX(
            'key measures',
            'Key Measures'[Lane 1 width]
        )
            + SUMX(
                'key measures',
                'Key Measures'[Lane 2 width]
            )
            + SUMX(
                'key measures',
                'Key Measures'[Lane 3 width]
            )
            + SUMX(
                'key measures',
                'Key Measures'[Lane 4 width]
            )
    )

But your key measure table has no data, it is just a measure table, so there is nothing to iterate over to compute the lane widths, which are measures (again) not fields, so referencing them as 'key measuers'[lane 4 width] is wrong - it works, but you reference measures as [Measure Name] not table[Measure Name] - and again, you cannot iterate over an empty table.

So this needs a bit more work than just answering how to construct a rolling total measure. 

I'd begin by starting over with the [amount 1] measure and put as much or all of the relevant code in there as possible. Going 3-4 levels deep makes it really hard to debug. I don't know if you know this, but when you reference one measure in another measure, an implicit CALCULATE() is wrapped around the referenced measure, which may or may not impact the results, depending on what the measure is doing. Trying to debug this would take a few hrs and ensure it returned the right results.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
DataNoobie
Helper I
Helper I

Thank you for looking at this @edhans. It doesn't work. I think it's because [Amount1] and [Amount2] are calculated DAX measures (not columns from a table). To do a cumulative amount on [Amount1], I used SUMMARIZE to create a virtual table.

 

Is it possible to calculate in DAX as a measure what can be calculated in Excel: cell B3 - cell C3 + cell D2 (where cell D2 is the running total copied down the column in Excel), with [Amount1] and [Amount2] being DAX measures?

 

I tried:

running total measure  :=
VAR IndexID = SELECTEDVALUE( 'ID_FromFactTable'[ID], 0 )

 

RETURN
 
SUMX (
FILTER (
SUMMARIZE ( ALLSELECTED ( 'ID_FromDIM_Table' ), 'ID_FromDIM_Table'[ID],
"_RunningTotal", [Amount1] - [Amount2]),
'ID_FromDIM_Table'[ID] <= IndexID),
[_RunningTotal] )

 

...but this gave me too large values, which made me think that it wasn't taking account of the report filters and slicers. 

Cumulative Total = 
VAR varIndex = MAX('Table'[IndexID])
RETURN
CALCULATE(
    [Amount1] - [Amount2]
    REMOVEFILTERS('Table'),
    'Table'[IndexID] <= varIndex
)

This should work, but you have yet to provide any data for me to test with. I would not use a summarize table the way you are without wrapping it in ADDCOLUMNS, but I think that is overkill.

 

Please provide data or a link to an actual PBIX file if the above doesn;t work @DataNoobie 

 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User III
Super User III

This works @DataNoobie - no need for SELECTEDVALUE here, and generally avoid ALLSELECTED unless absolutely necessary. It is one of the trickiest DAX functions.

edhans_0-1619558455737.png

Cumulative Total = 
VAR varIndex = MAX('Table'[IndexID])
RETURN
CALCULATE(
    SUMX(
        'Table',
        'Table'[Amount1] - 'Table'[Amount2]),
        REMOVEFILTERS('Table'),
        'Table'[IndexID] <= varIndex
)

 

I used random numbers because I couldn't use your image above. 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors