Running Total of a Complex Measure

[Anonymous]

I'm attempting to calculate a running total based off a complex measure. The measure is made using a series of calculations off a table in PowerPivot. I've imported the data set to PowerBI and I want to display this value as a month to month value, as well as a YTD cumulative value. Using PowerPivot, this was easily achieved by using the "Show Values As" feature. 

I'm trying to produce a graph like this:

 

 The "Monthly" Values in the graph is from the term [Fluid Lost] in the following formulas. The "2017 Cumulative" and "2018 Cumulative" are the running totals produced using the "Show Values As" function on a Pivot Table in Excel.

I've tried using this formula:

Cumulative Fluid Lost =

CALCULATE (

    ( [Fluid Lost] ),

    FILTER (

        ALL ( 'Master' ),

        'Master'[Month] <= MAX ( 'Master'[Month] )

    )

)

The problem with this is that it ignores the page level filters I have which need to be included. These filters are Year and Site (There are 6 different sites in the data set which need to be differentiated). A bad work around I considered is the following:

Site A 2018 Cumulative Fluid Lost =

CALCULATE (

    ( [Fluid Lost] ),

    FILTER (

        ALL( 'Master' ),

        'Master'[Month] <= MAX ( 'Master'[Month] )

    ),

    FILTER (

        ALL ( 'Master' ),

        'Master'[Year]=2018

    ),

    FILTER (

        ALL ( 'Master' ),   

        'Master'[Site]="Site A"

    )

)

The problem with this is that this will have to be reproduced for every site and the formula changed every year. We also lose the ability to quickly compare sites using slicers.

For some context here are the formulas used to calculate the [Fluid Lost] measure in PowerPivot:

Fluid Lost:=[Fluids Issued]-[Fluids Removed]-[Total Oil Burn]-[Change in Waste Tank]-[Total Exceptions]

[Fluids Issued], [Fluids Removed], [Change in Waste Tank], and [Total Exceptions] are simple Sums from the 'Master' table, but [Total Oil Burn] is a big calculation. Here is how that is calculated:

Total Oil Burn:=[Oil Burn A]+[Oil Burn B]+[Oil Burn C]+[Oil Burn D]+[Oil Burn E]+[OTHER Oil Burn]

where,

Oil Burn A:=[Fuel Burn A]*calculate(sum(OILBURN[Oil Burn Rate]),filter(OILBURN,[Type]="Type A"))

and, 

Fuel Burn A:=[Type A Op Hours]*CALCULATE(AVERAGE(Master[Quantity]),filter(master,[Entry Type]="Fuel Burn Rate"),filter(Master,[Material / Equipment]="Type A"))

and, 

Type A Op Hours:=CALCULATE(sum([Quantity]),filter('Master', [Entry Type]="Op Hours"),FILTER('Master',[Material / Equipment]="Type A"))

Any assistance would be must appreciated. I've hit a bit of a dead end here. I've found other threads regarding running totals, but they seem to deal with much simpler data sets that don't require the same filters as I do. I can't seem to adapt their methods to work for my problem.

Here are the other threads I've looked at:

https://community.powerbi.com/t5/Desktop/Cumulative-Total/td-p/43115

https://community.powerbi.com/t5/Desktop/Sum-of-values-in-a-measure-with-divide-measure/m-p/296768#M...

[OwenAuger]

Hi there,

The simplest change to your measure I can suggest is something like this:

Cumulative Fluid Lost =
VAR MaxMonth =
    MAX ( 'Master'[Month] )
RETURN
    CALCULATE ( [Fluid Lost], 'Master'[Month] <= MaxMonth )

As you mentioned, your previous version cleared all filters on the 'Master' table, not just Month. The above version modifies just the Month filter but leaves other filters unchanged.

In general, you may want to consider a separate Date table in order to handle cumulative and other date-based calculations. This would allow you to use time intelligence functions like DATESYTD, which might be useful based on your description.

Regards,

Owen

[Greg_Deckler · Accepted solution]

Look at replacing your ALL with ALLEXCEPT.

[Anonymous]

That worked perfectly, thank you. 

Would you have any idea how to produce the 2017 line? Ideally it would be the filtered year - 1 so that it automatically switches year to year. 

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a method of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Anonymous]

Thank you once again.