cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Running Total of a Complex Measure

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

1 ACCEPTED SOLUTION
Super User

Look at replacing your ALL with ALLEXCEPT.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
5 REPLIES 5
Super User

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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Super User

Look at replacing your ALL with ALLEXCEPT.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thank you once again.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors