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
- mail2rohitnagar1 year agoRegular Visitor
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