Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
And you can download the .pbix file for more details.
Best Regards,
Angelia
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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 ] )
Thanks all! These solutions all worked. I appreciate the help.
Jason
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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 ] )
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.
And you can download the .pbix file for more details.
Best Regards,
Angelia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |