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 have date such as the below where I'm looking to add a running total column:
Date | Postcode | Count running total |
01/01/2020 | AA1 1AA | 1 |
02/01/2020 | AA1 1AA | 1 |
02/01/2020 | AA1 1AA | 2 |
02/01/2020 | BB2 2BB | 1 |
02/01/2020 | AA1 1AA | 3 |
02/01/2020 | BB2 2BB | 2 |
Please can someone help me with the column formula I can use to get this running total?
Best regards,
Drew
Solved! Go to Solution.
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Hi @DrewCarousel ,
You could refer to @Greg_Deckler ' s suggestions to use DAX to achieve this goal, or you also could use M code like below to achieve this goal
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMDJR0lR0dDBUNHR6VYHaCEEUUSTk5GCkZOTuToiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Postcode = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Postcode", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Postcode", "Date"}, {{"all", each _, type table [Date=date, Postcode=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "a",1,1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"a"}, {"a"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"all"})
in
#"Removed Columns"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @DrewCarousel ,
You could refer to @Greg_Deckler ' s suggestions to use DAX to achieve this goal, or you also could use M code like below to achieve this goal
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMDJR0lR0dDBUNHR6VYHaCEEUUSTk5GCkZOTuToiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Postcode = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Postcode", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Postcode", "Date"}, {{"all", each _, type table [Date=date, Postcode=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "a",1,1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"a"}, {"a"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"all"})
in
#"Removed Columns"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
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 |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |