cancel
Showing results for
Did you mean:
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

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

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

Proud to be a Datanaut!

Highlighted
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

Member

Re: Calculate running total on a measure

Refer this video, hope this solve your challenge

4fD1YJrQ

Super Contributor

Re: Calculate running total on a measure

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

Proud to be a Datanaut!

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.

Announcements

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

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

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 258 members 2,942 guests
Recent signins: