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

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

Reply
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:

 Fluids Graph.PNG

 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
Greg_Deckler
Super User
Super User

Look at replacing your ALL with ALLEXCEPT.


Follow on LinkedIn
@ 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...

View solution in original post

5 REPLIES 5
OwenAuger
Super User
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
Twitter
LinkedIn
Greg_Deckler
Super User
Super User

Look at replacing your ALL with ALLEXCEPT.


Follow on LinkedIn
@ 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. 

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


Follow on LinkedIn
@ 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. 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.