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

Calculate running total on a measure

Hi,

 

I have a measure "ranked" that creates a numbered list starting at 1, based off the index in the source table. I can't use the index itself in the visual I need to filter by an item, so the index would be all over the place as i have many different items

 

Ranked = RANKX( ALLSELECTED( Table1 ) , CALCULATE( SUM( Table1[Index] ) ),,ASC,DENSE )
 
 When I try to calculate the SUM of PLANNED_QTY referencing the "Ranked" measure, this is the DAX expression I used:
 
COLUMN1 = CALCULATE(SUM('Table1'[PLANNED_QTY]),FILTER('Table1','Table1'[Ranked]<=EARLIER('Table1'[Ranked])))

 

The error message I get says the first argument of EARLIER/EARLIEST is not a valid column reference. From what I can tell, EARLIER does not work on a measure.

 

I also cannot filter by the date column as there are multiple instances of the same date, that is why I needed a list of 1-100+ using the rankx function

 

Is there any other way to calculate a running total on the PLANNED_QTY?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculate running total on a measure

HI @joefred77 

You can use the below code

Sales Running Total 2 = 
CALCULATE(
    SUM(Table1[PLANNED_QTY]),
    FILTER(
       ALLSELECTED(Table1),
       Table1[Index] <= MAX( Table1[Index] )
    )
)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 REPLIES 5
Stachu Super Contributor
Super Contributor

Re: Calculate running total on a measure

I don't really get the requirement here, can you share sample data? below is the post that guides on how to structure posts most efficiently
How to Get Your Question Answered Quickly 

as far as I can understand you need the sum of planned quanitity per item, correct? I don't really get the constraint on the use of date and why exactly the index/rank are needed, but that should become more clear once I see the data

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

Proud to be a Datanaut!

Highlighted
joefred77 Frequent Visitor
Frequent Visitor

Re: Calculate running total on a measure

 

Here is my data

 

Index, PLANNED_QTY and Date are columns, Ranked is the RANKX measure (based on Index when filtered by item)

 

Index      Item      Ranked      Date     PLANNED_QTY

313         A            1              6/5/19    -100

398         A            2              6/6/19    -200

409         A            3              6/6/19     500

 

What I need it to look like:

 

Index      Item          Ranked      Date     PLANNED_QTY    Running Total

313         A               1               6/5/19    -100                    -100

398         A               2               6/6/19    -200                    -300

409         A               3               6/6/19     500                      200

 

nsrshkh1 Member
Member

Re: Calculate running total on a measure

Refer this video, hope this solve your challenge

 

https://www.youtube.com/watch?v=f5k

4fD1YJrQ

Stachu Super Contributor
Super Contributor

Re: Calculate running total on a measure

these tables are the output, correct? how do the input tables look like?

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

Proud to be a Datanaut!

Super User
Super User

Re: Calculate running total on a measure

HI @joefred77 

You can use the below code

Sales Running Total 2 = 
CALCULATE(
    SUM(Table1[PLANNED_QTY]),
    FILTER(
       ALLSELECTED(Table1),
       Table1[Index] <= MAX( Table1[Index] )
    )
)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 258 members 2,942 guests
Please welcome our newest community members: