Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi there, I have some data that looks like this:
Period | Beginining Population |
Ice Age | 1 |
Renaissance | 50 |
Industrial Revolution | 200 |
My end goal is to calculate the change between periods but I'm struggling to figure out what the correct columns/measures are to calculate that:
Period | Ending Period | Beginining Population | Ending Population | Change |
Ice Age | Renaissance | 1 | 50 | 49 |
Renaissance | Industrial Revolution | 50 | 200 | 150 |
Industrial Revolution | NA | 200 | NA | NA |
What would the simplest way to calculate the above? Thanks so much!
Solved! Go to Solution.
Hey,
This is best done in Power Query. Here is the code for the query that you can place in the Advanced Editor. The code is exactly based on your example above.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kxOVXBMT1XSUTJUitWJVgpKzUvMLC5OzEsGiZkagAU981JKi0uKMhNzFIJSy/JzSksy8/OA0kYGQPlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, #"Beginning Population" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type text}, {"Beginning Population", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "NextIndex", each [Index] + 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"NextIndex"}, #"Added Custom", {"Index"}, "Added Custom", JoinKind.LeftOuter),
#"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Period", "Beginning Population"}, {"Ending Period", "Ending Population"}),
#"Inserted Subtraction" = Table.AddColumn(#"Expanded Added Custom", "Population Change", each [Ending Population] - [Beginning Population], Int64.Type),
#"Removed Other Columns" = Table.SelectColumns(#"Inserted Subtraction",{"Period", "Beginning Population", "Ending Period", "Ending Population", "Population Change"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Period", "Ending Period", "Beginning Population", "Ending Population", "Population Change"})
in
#"Reordered Columns"
Please let me know if you have any questions.
Hi @Anonymous ,
Provide another way of thinking:
1. First, create a new Index column in Power Query Eidtor to indicate the order of Periods.
2. Then create 3 measures:
Ending Period =
VAR _index =
MAX ( 'Table'[Index] ) + 1
VAR _period =
MAXX (
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] = _index ),
'Table'[Period]
)
VAR _result =
IF ( _period <> BLANK (), _period, "NA" )
RETURN
_result
Ending Population =
VAR _index =
MAX ( 'Table'[Index] ) + 1
VAR _population =
MAXX (
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] = _index ),
'Table'[Beginining Population]
)
VAR _result =
IF ( _population <> BLANK (), _population, "NA" )
RETURN
_result
Change =
VAR _index =
MAX ( 'Table'[Index] ) + 1
VAR _beg_population =
MAX('Table'[Beginining Population])
VAR _population =
MAXX (
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] = _index ),
'Table'[Beginining Population]
)
VAR _change = _population - _beg_population
VAR _result =
IF ( _population <> BLANK (), _change, "NA" )
RETURN
_result
3.Result:
Best Regards,
changqing
Hey,
This is best done in Power Query. Here is the code for the query that you can place in the Advanced Editor. The code is exactly based on your example above.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kxOVXBMT1XSUTJUitWJVgpKzUvMLC5OzEsGiZkagAU981JKi0uKMhNzFIJSy/JzSksy8/OA0kYGQPlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, #"Beginning Population" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type text}, {"Beginning Population", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "NextIndex", each [Index] + 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"NextIndex"}, #"Added Custom", {"Index"}, "Added Custom", JoinKind.LeftOuter),
#"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Period", "Beginning Population"}, {"Ending Period", "Ending Population"}),
#"Inserted Subtraction" = Table.AddColumn(#"Expanded Added Custom", "Population Change", each [Ending Population] - [Beginning Population], Int64.Type),
#"Removed Other Columns" = Table.SelectColumns(#"Inserted Subtraction",{"Period", "Beginning Population", "Ending Period", "Ending Population", "Population Change"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Period", "Ending Period", "Beginning Population", "Ending Population", "Population Change"})
in
#"Reordered Columns"
Please let me know if you have any questions.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
20 | |
18 | |
17 | |
16 |
User | Count |
---|---|
34 | |
21 | |
19 | |
18 | |
11 |