cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
facilitydax Frequent Visitor
Frequent Visitor

Struggling with a Running Total Measure

I've been struggling with a Running Total measure for a while now.  The problem is that most examples of running total measures that are out there have to do with using a "column" as the sorting mechanism but I need to a measure.

1. In this example, I have the "RankTest" measure which is using a prioritization matrix to rank all my assets from 1-n. 

2. Next I have a restoration cost measure that is being calculated for every asset which is "Unconstrained_RM(SUM)".

 

However, as you can see I can't figure out how to create a measure for the running total in order by the RankTest.

runningTotalProblem.png

This is the current measure called Agg_Test5 that I've come up but it just replicates my UnconstrainedRMSum.

Agg_Test5 =
VAR currank = CALCULATE([RankTest], FILTER('AssetTable', SELECTEDVALUE('AssetTable'[AssetNumber])))
RETURN
CALCULATE (
SUMX('AssetTable', [UNCONSTRAINED_RM(SUM)]),
FILTER('AssetTable', [RankTest] <= currank),
ALL ('AssetTable'[AssetNumber])
)
Thanks in advance.  
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Struggling with a Running Total Measure

Hi @facilitydax 

Try this. See it all at work in the attached file.

Running Total 2 = 
VAR currank2 = [RankTest]
RETURN
    CALCULATE (
        SUMX(Asset; [RM]);
        FILTER(ALL(Asset); [RankTest] <= currank2)
)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

6 REPLIES 6
Super User
Super User

Re: Struggling with a Running Total Measure

Hi @facilitydax 

Can you share the pbix? or a simplified version that reproduces the problem?

Super User
Super User

Re: Struggling with a Running Total Measure

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

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

facilitydax Frequent Visitor
Frequent Visitor

Re: Struggling with a Running Total Measure

@AlB ,

Here's a highly modified version of the file with just the top 100 rows in one table. However, the concept is the same in that I need to be able to do the Running Total correctly.

 

Asset Running Total Problem

 

Thanks!

Super User
Super User

Re: Struggling with a Running Total Measure

Hi @facilitydax 

Try this. See it all at work in the attached file.

Running Total 2 = 
VAR currank2 = [RankTest]
RETURN
    CALCULATE (
        SUMX(Asset; [RM]);
        FILTER(ALL(Asset); [RankTest] <= currank2)
)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

facilitydax Frequent Visitor
Frequent Visitor

Re: Struggling with a Running Total Measure

Hi @AlB ,

Can't thank you enough for the help so far. It does indeed work in that stripped down troubleshooting file. Unfortunately, I can't verify whether it works in my full larger file model because it exceeds my memory after a minute or so.  It's kind of a bummer because the rank measure and the unconstrainedRM measure render just fine until I try to throw the new running total into the table.

 

If you have any ideas on why it won't finish, give me a holler.

 

Thanks! 

Super User
Super User

Re: Struggling with a Running Total Measure

Hard to tell without seeing the real file. Can you show at least the structure of your tables? How many rows do they have? How much memory does your computer have? 

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 414 members 3,935 guests
Please welcome our newest community members: