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

[DataNoobie]

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?

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

[DataNoobie]

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.

[edhans]

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. 

[DataNoobie]

@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. 

[edhans]

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.

[DataNoobie]

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. 

[edhans]

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.

[edhans]

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

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.