cancel
Showing results for
Did you mean:
Highlighted
fendjas Frequent Visitor

## Conditional incremental subtraction from an original number?

Hi all,

Here is my data I want to be able to subtract the first two columns, but then take that number and create a new column that stores that value in the next index - so like an incremental subtraction from the original value. However if the subtraction yields a negative number, it just defaults to 0. The idea is that when it gets to a new StockCode, it will repeat the process. How can this be done?

Jas

4 ACCEPTED SOLUTIONS

Accepted Solutions v-huizhn-msft
Microsoft

## Re: Conditional incremental subtraction from an original number?

Hi @fendjas,

1. Add an index column by add index column in Power Query Editor, please review more details here.

2. Create a calculated column [column] using the formula.

```Column =
VAR Y =
MAXX ( ALLEXCEPT ( Test, Test[Index] ), Test[Index] )
RETURN
CALCULATE ( SUM ( Test[PastDue+MRP] ), FILTER ( Test, Test[Index] <= Y ) )
```

3. Create [column 2] using the formula.

`Column 2 = MAXX(FILTER(Test,Test[StockCode]=EARLIER(Test[StockCode])),Test[FG])`

4. Create [column 3] using the formula.

```Column 3 =
VAR Y =
MAXX ( ALLEXCEPT ( Test, Test[Index] ), Test[Index] )
RETURN
Test[Column]
- MAXX (
FILTER ( Test, Test[StockCode] = EARLIER ( Test[StockCode] ) ),
IF (
Test[FG] <> 0,
LOOKUPVALUE ( Test[Column], Test[Index], Test[Index] - 1 ),
0
)
)
```

5. Create another calculated column to get right result based on columns above.

`FGBal = IF((Test[Column 2]-Test[Column 3])>0,(Test[Column 2]-Test[Column 3]),0)` Best Regards,
Angelia Super User

## Re: Conditional incremental subtraction from an original number?

Please paste this code into the advanced editor and follow the steps. Then replace the code in step "Source" by a reference to your table:

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MACSpgZKsToQARDXDJVrAuE6AZlGYOWGRggREN8YlWuBygVpjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [StockCode = _t, FG = _t, #"PastDue+MRP" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StockCode", type text}, {"FG", Int64.Type}, {"PastDue+MRP", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Added Index", {"StockCode"}, {{"All", each _, type table}}),
"FGBal",
(x) => List.Max({0,
if x[FG] = 0
then [All]{0}[FG] - List.Sum(Table.SelectRows([All],
(y) => y[Index]<=x[Index])[#"PastDue+MRP"])
else x[FG]-x[#"PastDue+MRP"]
})
)
),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"FG", "PastDue+MRP", "FGBal"}, {"FG", "PastDue+MRP", "FGBal"})
in
#"Expanded Custom"```

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann Super User

## Re: Conditional incremental subtraction from an original number?

Hi @fendjas

This could just be another way of doing it

```Column =
VAR Cumulative =
SUMX (
FILTER (
Table1,
[Index] <= EARLIER ( [Index] )
&& [Stock Code] = EARLIER ( [Stock Code] )
),
[ FG ] - [ MRPQty ]
)
VAR PreviousCumulative =
SUMX (
FILTER (
Table1,
[Index] < EARLIER ( [Index] )
&& [Stock Code] = EARLIER ( [Stock Code] )
),
[ FG ] - [ MRPQty ]
)
RETURN
IF ( Cumulative < 0, 0, PreviousCumulative + [ FG ] - [ MRPQty ] )``` Try my new Power BI game Cross the River
fendjas Frequent Visitor

## Re: Conditional incremental subtraction from an original number?

Thanks all! These solutions all worked. I appreciate the help.

Jason

4 REPLIES 4 v-huizhn-msft
Microsoft

## Re: Conditional incremental subtraction from an original number?

Hi @fendjas,

1. Add an index column by add index column in Power Query Editor, please review more details here.

2. Create a calculated column [column] using the formula.

```Column =
VAR Y =
MAXX ( ALLEXCEPT ( Test, Test[Index] ), Test[Index] )
RETURN
CALCULATE ( SUM ( Test[PastDue+MRP] ), FILTER ( Test, Test[Index] <= Y ) )
```

3. Create [column 2] using the formula.

`Column 2 = MAXX(FILTER(Test,Test[StockCode]=EARLIER(Test[StockCode])),Test[FG])`

4. Create [column 3] using the formula.

```Column 3 =
VAR Y =
MAXX ( ALLEXCEPT ( Test, Test[Index] ), Test[Index] )
RETURN
Test[Column]
- MAXX (
FILTER ( Test, Test[StockCode] = EARLIER ( Test[StockCode] ) ),
IF (
Test[FG] <> 0,
LOOKUPVALUE ( Test[Column], Test[Index], Test[Index] - 1 ),
0
)
)
```

5. Create another calculated column to get right result based on columns above.

`FGBal = IF((Test[Column 2]-Test[Column 3])>0,(Test[Column 2]-Test[Column 3]),0)` Best Regards,
Angelia Super User

## Re: Conditional incremental subtraction from an original number?

Please paste this code into the advanced editor and follow the steps. Then replace the code in step "Source" by a reference to your table:

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MACSpgZKsToQARDXDJVrAuE6AZlGYOWGRggREN8YlWuBygVpjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [StockCode = _t, FG = _t, #"PastDue+MRP" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StockCode", type text}, {"FG", Int64.Type}, {"PastDue+MRP", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Added Index", {"StockCode"}, {{"All", each _, type table}}),
"FGBal",
(x) => List.Max({0,
if x[FG] = 0
then [All]{0}[FG] - List.Sum(Table.SelectRows([All],
(y) => y[Index]<=x[Index])[#"PastDue+MRP"])
else x[FG]-x[#"PastDue+MRP"]
})
)
),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"FG", "PastDue+MRP", "FGBal"}, {"FG", "PastDue+MRP", "FGBal"})
in
#"Expanded Custom"```

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann Super User

## Re: Conditional incremental subtraction from an original number?

Hi @fendjas

This could just be another way of doing it

```Column =
VAR Cumulative =
SUMX (
FILTER (
Table1,
[Index] <= EARLIER ( [Index] )
&& [Stock Code] = EARLIER ( [Stock Code] )
),
[ FG ] - [ MRPQty ]
)
VAR PreviousCumulative =
SUMX (
FILTER (
Table1,
[Index] < EARLIER ( [Index] )
&& [Stock Code] = EARLIER ( [Stock Code] )
),
[ FG ] - [ MRPQty ]
)
RETURN
IF ( Cumulative < 0, 0, PreviousCumulative + [ FG ] - [ MRPQty ] )``` Try my new Power BI game Cross the River
fendjas Frequent Visitor

## Re: Conditional incremental subtraction from an original number?

Thanks all! These solutions all worked. I appreciate the help.

Jason

Announcements #### New Topics Started Badges Coming  