The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
CntrlRunTot is a hardcoded column of values. I'm trying to make a calculated column that subtracts its previous row from the demand qty that corresponds to its row and have that product stored in the row below. Ex. 188535 - 2976 = 185559 which is then stored a row beneath the 188535 row. Right now my Running Total calculated column depends on every value of CntrlRunTot but I need it to just depend on that first number 188535 and then be able to subtract Demand Qty and store the following values within its consecutive rows.
Current Setup
Solved! Go to Solution.
Hi @Anonymous ,
You can achieve it by DAX or Power Query method, please see the details in the attachment.
Power Query method:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcjBDQAhCAXRXv6ZgwIi1kLsv411ZRPdwySTFwEQGptoWVO/MCngquN9At/c2T1ZVmW3Xaqk689tdEtvx+cD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Demand Qty" = _t, CntrlRunTot = _t, Index = _t, FindNegVals = _t, NegVals = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Demand Qty", Int64.Type}, {"CntrlRunTot", Int64.Type}, {"Index", Int64.Type}, {"FindNegVals", Int64.Type}, {"NegVals", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "NextRowDQTY", each (try #"Changed Type" [Demand Qty] { [Index] } otherwise null)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Running Total", each List.Max(#"Added Custom"[CntrlRunTot])-List.Sum(List.FirstN(#"Added Custom"[NextRowDQTY],[Index]))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"NextRowDQTY"})
in
#"Removed Columns"
Refer to Previous Row and Next Row in Power Query
DAX method:
1. Create a calculatd column to get the next row value
Nextrow Value =
CALCULATE (
MAX ( 'Table'[Demand Qty] ),
FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 )
)
2. Create a calculated column as below to get the running total values
Col_Running Total =
CALCULATE ( MAX ( 'Table'[CntrlRunTot] ), ALL ( 'Table' ) )
- CALCULATE (
SUM ( 'Table'[Nextrow Value] ),
FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) )
)
Power BI: Get value from next row
Best Regards
Hi @Anonymous ,
I believe the 'Value from previous row' section from https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/power-bi-data-sources/power-query-get-value-from-previous-row/ addresses your problem.
For your example:
1. Create a second index column (Index.2) with 1 subtracted from it
2. Merge the table with itself, joining on Index and Index.2
3. Expand the table pulling in the new Demand Qty column
4. Create new column calculated or custom that subtracts the new Dmand Qty from Cntrlruntot
Small example result below:
I need the Custom column to only rely on the first value of the CntrlRunTot column. I need the difference of (in the example photo attached) "x - z" to be displayed in the same column as "x", so the following row where value "y" is located. Right now Custom is still depending on all of the values in CntrlRunTot.
Hi @Anonymous ,
Try using List.Max(#"Changed Type"[CntrlRunTot]) - [Subtracted from Column.Demand Qty]
Do I add the List.Max function into the custom column like this or? (if so it gives me an error)
Hi @Anonymous ,
You can achieve it by DAX or Power Query method, please see the details in the attachment.
Power Query method:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcjBDQAhCAXRXv6ZgwIi1kLsv411ZRPdwySTFwEQGptoWVO/MCngquN9At/c2T1ZVmW3Xaqk689tdEtvx+cD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Demand Qty" = _t, CntrlRunTot = _t, Index = _t, FindNegVals = _t, NegVals = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Demand Qty", Int64.Type}, {"CntrlRunTot", Int64.Type}, {"Index", Int64.Type}, {"FindNegVals", Int64.Type}, {"NegVals", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "NextRowDQTY", each (try #"Changed Type" [Demand Qty] { [Index] } otherwise null)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Running Total", each List.Max(#"Added Custom"[CntrlRunTot])-List.Sum(List.FirstN(#"Added Custom"[NextRowDQTY],[Index]))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"NextRowDQTY"})
in
#"Removed Columns"
Refer to Previous Row and Next Row in Power Query
DAX method:
1. Create a calculatd column to get the next row value
Nextrow Value =
CALCULATE (
MAX ( 'Table'[Demand Qty] ),
FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 )
)
2. Create a calculated column as below to get the running total values
Col_Running Total =
CALCULATE ( MAX ( 'Table'[CntrlRunTot] ), ALL ( 'Table' ) )
- CALCULATE (
SUM ( 'Table'[Nextrow Value] ),
FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) )
)
Power BI: Get value from next row
Best Regards
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
246 | |
119 | |
114 | |
86 | |
70 |