Forum Discussion
Need help with calculated column
- 1 year ago
Hi
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1djbbtowAAbgV0G5hsgH4oTLduukIjFQe9GLqqooyzYkaKoUOu1mF3urvc6eZHYI1A7GJwXHk1pyahM+nIQ///19NIz6EQIooZO75Wq1nK/p3KwsvmwXm1c6Oy6+P9PJZLkoi9fi66YxP8WE/evf3396OAbZfhb0M4z7GNN1gK4DMaxeMZtgQLfFGLMDJ78QoVP6c/FSDqp3wQ74yHb7COAADAf1m2O7hXGa9T6UdKm3PyC3COixdoswxqA3oOvYrh/691ESCHIy/ylDJjzynaFGVhtqJNsyjHFWYUkg2PH2WYYlIhYTEyyKU8RjSQzTCpsGg13JsCmPxYYjCxojm8QZrLBZINiL7TcZNmvjNMb7kR0Fgr3NX2TYURvYw2kMQSDa6WIj0ULQrhYGov1cvMm0sF0tCkT7MV/ItKhd7Q5LusaO5/TbhxxdttXQkrawKBDsp/xJhkWtYnEg2Mm8lGFxq1guFt8UT3nJBLd5+bZc5AbWWfEjLy+vD28GpvUs7BNC6G+61+LqFbDjJKBadzITz6Z3VzeX18exuIFlhzuRK+gG4auW7qIRj7vDitmYx6risTGWbWnG4+6wYjbmsaQdLL1oGvG4S+zqBDZtFZuFgBWzMY9txmNoiCVS7CgErJiNeawqHtuMLDqKx91pxWzMaZXx2P485uNxd1oxG/NaVTx20KIQtGI25rWqeOygjQ4hqsMbspCN+cu2GY+xIXYoxaIQsGI25rGqeGwzsqAZj7sMUeUJrCoe25/Gutb4ar2eK7TcswA6e2sMxNYYmbTGuz88jsedYk3aYxRnwARbfzDofQmb9se+uA79sWJsxf6YfUxG/bE/rHV/rBnbkTC2NVfXIHu7bu0b5JNcdNQg1096ugbZF9ahQTbGDunzgVmD7Evr0CA7aHUNsi+tQ4PsoNU1yL60Dg2ygzZS94zebsn2DbI9Vtcg+8I6NMj2WF2D7C9IWTfI9lhNg6yx/h8Nsiwid4c9e4NcP/xoGmQ/2EAaZF/YIBpkT9dsGA2yH2woDbIfbSgNsh9tKA2yH61hgwwNtbulDPNLZh2yp1vyeTtkuH+I13TIfrCBdMi+YpSHDvnhHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MONTH = _t, YEAR = _t, #"PRINCIPAL MANAGERS" = _t, #"PROD/ SERVICE" = _t, #"SALES PERSON" = _t, PRINCIPAL = _t, PRATICULARS = _t, REFERENCE = _t, #"REV, RS. LAKHS" = _t, REV = _t, #"MARGIN %" = _t, #"MARGIN (RS. LAKHS)" = _t, MARGIN = _t, #"Financial Year" = _t, #"BUD. / ACT." = _t, #"MONTH YEAR" = _t, #"Unique Id" = _t, #"Actual Sales" = _t, #"Target in Cr" = _t, #"Target Margin in Cr" = _t, Variance = _t, CorrectedTarget = _t]), Clean1 = Table.ReplaceValue(Source, "₹", "", Replacer.ReplaceText, {"Target in Cr", "Actual Sales"}), Clean2 = Table.ReplaceValue(Clean1, "Cr", "", Replacer.ReplaceText, {"Target in Cr", "Actual Sales"}), ChangedTypes = Table.TransformColumnTypes(Clean2, { {"MONTH", Int64.Type}, {"YEAR", Int64.Type}, {"Target in Cr", type number}, {"Actual Sales", type number} }), #"Removed Old CT" = Table.RemoveColumns(ChangedTypes, {"CorrectedTarget"}), Sorted = Table.Sort(#"Removed Old CT", { {"SALES PERSON", Order.Ascending}, {"PRATICULARS", Order.Ascending}, {"YEAR", Order.Ascending}, {"MONTH", Order.Ascending} }), Columns = Table.ColumnNames(Sorted), Rows = Table.ToRows(Sorted), AddCorrectedTarget = List.Accumulate( Rows, { {}, 0, null, null }, (state, currentRow) => let result = state{0}, prevCT = state{1}, prevSP = state{2}, prevPart = state{3}, salesPerson = currentRow{List.PositionOf(Columns, "SALES PERSON")}, particular = currentRow{List.PositionOf(Columns, "PRATICULARS")}, target = try Number.From(currentRow{List.PositionOf(Columns, "Target in Cr")}) otherwise 0, actual = try Number.From(currentRow{List.PositionOf(Columns, "Actual Sales")}) otherwise 0, isFirst = List.Count(result) = 0 or salesPerson <> prevSP or particular <> prevPart, newCT = if isFirst then target else prevCT - actual + target, rowWithCT = Record.AddField(Record.FromList(currentRow, Columns), "CorrectedTarget", newCT), updatedRows = List.Combine({result, {rowWithCT}}) in {updatedRows, newCT, salesPerson, particular} ){0}, FinalTable = Table.FromRecords(AddCorrectedTarget) in FinalTable
Regards,
Karpurapu D.
Hey mail2rohitnagar ,
To calculate the Corrected Target in Power BI based on the previous month's Variance, you can use DAX either as a calculated column (if the model is at row level per month) or as a measure (if you're working with visuals or aggregations). If you're using a calculated column assume your table is named 'Sales' and columns: SalesPerson, Particular, Month, Year, Target, ActualSales.
Step 1: Create a column for Variance:
Variance = 'Sales'[CorrectedTarget] - 'Sales'[ActualSales]
Step 2: Create the Corrected Target column:
CorrectedTarget =
VAR PrevMonth =
CALCULATE(
MAX('Sales'[Month]),
FILTER(
'Sales',
'Sales'[SalesPerson] = EARLIER('Sales'[SalesPerson]) &&
'Sales'[Particular] = EARLIER('Sales'[Particular]) &&
'Sales'[Year] = EARLIER('Sales'[Year]) &&
'Sales'[Month] = EARLIER('Sales'[Month]) - 1
)
)
VAR PrevVariance =
CALCULATE(
'Sales'[Target] - 'Sales'[ActualSales],
FILTER(
'Sales',
'Sales'[SalesPerson] = EARLIER('Sales'[SalesPerson]) &&
'Sales'[Particular] = EARLIER('Sales'[Particular]) &&
'Sales'[Year] = EARLIER('Sales'[Year]) &&
'Sales'[Month] = PrevMonth
)
)
RETURN 'Sales'[Target] + PrevVariance
If using a measure, it's more complex because measures are aggregate-level and cannot store previous row context. You can still do it using CALCULATE and FILTER with a measure, but it's less recommended unless used in visuals.
For Detailed Information:
Calculated Columns in Power BI
Measures vs Calculated Columns
If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif Azam
Didn't work, getting an error as there are multiple lines returining for the formula. I have update the data set in the thread to better express my scenario