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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
ratna
Employee
Employee

Running total that outputs "0" if the sum is negative, and resets running total on next row + Power

 

Hi All,

I am trying to create a formula that calculates the running total of column A.

However, when the running total is negative, two things should happen:

  1.      the output should be 0
  2.      The sum should start anew on the next row.

And most importantly... the formula cannot use column B as a reference. The formula being unable to reference itself may seem nonsensical, but the desired output is a logic puzzle of sorts. Later when using PowerPivot and power bi this concept will be important.

Is this possible with one fluid formula?

Any help is appreciated!

Hours

Wrong Running Total of [Hours]

Desired Output

Comments

2

2

2

 

4

6

6

 

-2

4

4

 

-8

-4

0

Because B5<0, the desired output should be 0

0

-4

0

New running total starting at A6

1

-3

1

 

0

-3

1

 

8

5

9

 

-8

-3

1

 

-2.5

-5.5

0

 

1

-4.5

1

 

Thank you for your time.

8 REPLIES 8
wdx223_Daniel
Super User
Super User

it would be easier to use M to resolve this.

wdx223_Daniel_0-1613530309584.png

 

hi.

I need the formula you wrote in this picture and I wrote that but did'nt work for me and I met with a cyclic refrence Error.:(((

Hi Daniel,
Thank you for the response. I cannot use the M query as the main column is depending on other columns from different tables.

lbendlin
Super User
Super User

OK, I give up. I know that technically DAX does not support recursions beyond the simple aggregator functions, yet the solution still feels to be somewhat within reach (with a calculated table variable, I am sure).  But this is not my time to solve it.

lbendlin
Super User
Super User

I am still thinking about it. Meanwhile here is a "solution".  (It's a joke, just to be clear). zero based index added in PQ

Table Puzzle:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlKK1YlWMgGTuhCOrgWYMgCThkhsC2RpXSM9U5iKWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hours = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hours", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

 

Measure:

 

Result = 
var i = max(Puzzle[Index])
return sumx(filter(all(Puzzle),Puzzle[Index]<=i),Puzzle[Hours]+if(Puzzle[Index]=3,4,0)+if(Puzzle[Index]=9,1.5,0))

 

 

 

Does it need to be a measure or can it be a calculated column?

 

Both will work sir

lbendlin
Super User
Super User

Is it acceptable to add an index column? And this has to be DAX, not M ?

Thank you for the response Ibendlin. Yes, it is acceptable and this had to be written in dax.

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.