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 I am trying to find values based on column dependencies.
eg: I have below dataset
YearMonth | B | C | D | E | F | G |
2021-Jan | $100 | $50 | $20 | $10 | $5 | $115 |
2021-Feb | $55 | $30 | $20 | $10 | ||
2021-Mar | $60 | $25 | $6 | $11 | ||
2021-Apr | $50 | $28 | $9 | $5 | ||
2021-May | $45 | $35 | $10 | $20 | ||
2021-Jun | $50 | $20 | $12 | $8 | ||
2021-Jul | $80 | $20 | $10 | $5 | ||
2021-Aug | $50 | $20 | $15 | $5 |
G is calculated by B+C-D-E-F for jan
for 2021-Feb B should be 115 that we had for column G in 2021-Jan. I do have values for C,D,E,F for all months.
for 2021-Feb G will be 115+55-30-20-10=110
for 2021-Mar B will be 110
The above process has to be repeated.
How to obtain this in power bi desktop.I am new to power bi.
Solved! Go to Solution.
Here is an implementation in Power Query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtT1SsxT0lFSMTQwAFGmYNLIACIEFlGK1YEqdUtNAooogEVNQaQxqmq4Qt/EIphCM4gSsHIzsDpDhDrHArg6qMUWINISzV7fxEqYMhOIvaYI9xkh2etVmodmHsRpRiDSAlldDkydBT4PO5amYzXPFKouFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YearMonth = _t, B = _t, C = _t, D = _t, E = _t, F = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"YearMonth", type date}, {"B", Currency.Type}, {"C", Currency.Type}, {"D", Currency.Type}, {"E", Currency.Type}, {"F", Currency.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "G", each List.Accumulate({0..[Index]},#"Changed Type"{0}[B],(c,s)=> c+#"Added Index"[C]{s}-#"Added Index"[D]{s}-#"Added Index"[E]{s}-#"Added Index"[F]{s})),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom",each [B], each if [Index]=0 then [B] else #"Added Custom"{[Index]-1}[G],Replacer.ReplaceValue,{"B"})
in
#"Replaced Value"
Here it is as a calculated column, but you cannot fill column B.
G = SUMX(FILTER(Table,[YearMonth]<=EARLIER([YearMonth])), [B]+[C]-[D]-[E]-[F])
Hi,
You may download my PBI file from here.
Hope this helps.
@Ashish_Mathur @lbendlin Thank you very much both those solutions are working as expected..Thanks a lot.
You are welcome.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @ankababu007 ,
Agree with the solution given by @lbendlin , it is really a good choice to solve this problem in power query.
If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.
Best Regards,
Henry
Here is an implementation in Power Query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtT1SsxT0lFSMTQwAFGmYNLIACIEFlGK1YEqdUtNAooogEVNQaQxqmq4Qt/EIphCM4gSsHIzsDpDhDrHArg6qMUWINISzV7fxEqYMhOIvaYI9xkh2etVmodmHsRpRiDSAlldDkydBT4PO5amYzXPFKouFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YearMonth = _t, B = _t, C = _t, D = _t, E = _t, F = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"YearMonth", type date}, {"B", Currency.Type}, {"C", Currency.Type}, {"D", Currency.Type}, {"E", Currency.Type}, {"F", Currency.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "G", each List.Accumulate({0..[Index]},#"Changed Type"{0}[B],(c,s)=> c+#"Added Index"[C]{s}-#"Added Index"[D]{s}-#"Added Index"[E]{s}-#"Added Index"[F]{s})),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom",each [B], each if [Index]=0 then [B] else #"Added Custom"{[Index]-1}[G],Replacer.ReplaceValue,{"B"})
in
#"Replaced Value"
@lbendlin Thank you very much for your solution. Its working perfectly. Just curious if we can do this using DAX.
Here it is as a calculated column, but you cannot fill column B.
G = SUMX(FILTER(Table,[YearMonth]<=EARLIER([YearMonth])), [B]+[C]-[D]-[E]-[F])
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 |
---|---|
106 | |
105 | |
80 | |
68 | |
61 |
User | Count |
---|---|
143 | |
104 | |
103 | |
82 | |
70 |