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

Conditional incremental subtraction from an original number?

Hi all,

 

Here is my data

 

Capture.JPG

 

 

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. 

 

Capture2.JPG

 

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
Microsoft v-huizhn-msft
Microsoft

Re: Conditional incremental subtraction from an original number?

Hi @fendjas,

Please follow the steps below and you will get expected result.

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)

Please review result as follows.

1.GIF

 

And you can download the .pbix file for more details.

Best Regards,
Angelia

View solution in original post

Super User
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}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"StockCode"}, {{"All", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn([All], 
                                                                                      "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"]
                                                                                                        })
                                                                                        )
                                     ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"}),
    #"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

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Super User
Super User

Re: Conditional incremental subtraction from an original number?

Hi @fendjas

 

This could just be another way of doing it

After adding the Index Column from Query Editor...add this calculated column

 

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

cond.png

 

Try my new Power BI game Cross the River

View solution in original post

fendjas Frequent Visitor
Frequent Visitor

Re: Conditional incremental subtraction from an original number?

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

 

Jason

View solution in original post

4 REPLIES 4
Microsoft v-huizhn-msft
Microsoft

Re: Conditional incremental subtraction from an original number?

Hi @fendjas,

Please follow the steps below and you will get expected result.

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)

Please review result as follows.

1.GIF

 

And you can download the .pbix file for more details.

Best Regards,
Angelia

View solution in original post

Super User
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}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"StockCode"}, {{"All", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn([All], 
                                                                                      "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"]
                                                                                                        })
                                                                                        )
                                     ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"}),
    #"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

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Super User
Super User

Re: Conditional incremental subtraction from an original number?

Hi @fendjas

 

This could just be another way of doing it

After adding the Index Column from Query Editor...add this calculated column

 

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

cond.png

 

Try my new Power BI game Cross the River

View solution in original post

fendjas Frequent Visitor
Frequent Visitor

Re: Conditional incremental subtraction from an original number?

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

 

Jason

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)