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 this dataset which i want to put into a cumulative graph when filtered on the event through a slicer.
This will be put into a line graph which will change depending on which event is selected.
It needs to show amount payed, by number of days by event. I.e if a payment came in on day 1 (column daydiff), and the next on day 3 the Cumulative sum needs to show that order so we can see a progress of payments over time for that event- so the days ascending.
I need to show prov, paid and total paid so i will need 3 different dax measures i'm assuming.
DayDiff | prov | paid | Total_paid | event |
1227 | 0 | 9795.77 | 9795.77 | Event3 |
1092 | 0 | 5999.28 | 5999.28 | Event3 |
207 | 0 | 619.83 | 619.83 | Event3 |
218 | 0 | -75 | -75 | Event3 |
428 | 0 | -150 | -150 | Event3 |
645 | 0 | -125 | -125 | Event3 |
479 | 0 | 1000 | 1000 | Event 2 |
4 | 0 | 3051.91 | 3051.91 | Event 2 |
4692 | 0 | 6883.15 | 6883.15 | Event 2 |
4434 | 0 | 1521.75 | 1521.75 | Event 2 |
5 | 0 | 9000 | 9000 | Event3 |
798 | 0 | 22062.02 | 22062.02 | Event3 |
0 | 0 | 0 | 0 | Event3 |
14 | 6000 | 0 | 6000 | Event3 |
16 | -6000 | 6000 | 0 | Event 1 |
1977 | 0 | 0 | 0 | Event 1 |
8 | 3441 | 0 | 3441 | Event 1 |
3 | -3441 | 0 | -3441 | Event 1 |
47 | -506 | 505.83 | -0.17 | Event 1 |
1 | 506 | 0 | 506 | Event 1 |
Solved! Go to Solution.
Hi @Anonymous
Try this one- i am just repeating the cumulative step 3 times - this should work if I get the referensing right. Sorry I do not have access to pbi at the moment. Please let.me know if this does not work.
(MyTable as table) as table => let Source = Table.Buffer(MyTable), Add1=Table.AddColumn(Source, "Running Sum (paid)", each null, type number), Add2=Table.AddColumn(Add1, "Running Sum (prov)", each null, type number), Add3=Table.AddColumn(Add2, "Running Sum (total paid)", each null, type number), TableType = Value.Type(Add3), Sorted = Table.Sort(Source,{{"DayDiff", Order.Ascending}}), Cumulative1 = List.Skip(List.Accumulate(Sorted[paid],{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + cost})), Cumulative2 = List.Skip(List.Accumulate(Sorted[prov],{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + cost})), Cumulative3 = List.Skip(List.Accumulate(Sorted[total paid],{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + cost})), AddedRunningSum = Table.FromColumns(Table.ToColumns(Sorted)&{Cumulative1}&{Cumulative2}&{Cumulative3}, TableType) in AddedRunningSum
Kind regards,
JB
Hi @Anonymous ,
Slightly adapting this: https://community.powerbi.com/t5/Desktop/How-to-do-a-running-Sum-by-group-in-Power-Query/td-p/290123
This is the modified cumulative function (AddedRunningSum😞
(MyTable as table) as table =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
Sorted = Table.Sort(Source,{{"DayDiff", Order.Ascending}}),
Cumulative = List.Skip(List.Accumulate(Sorted[paid],{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + cost})),
AddedRunningSum = Table.FromColumns(Table.ToColumns(Sorted)&{Cumulative},TableType)
in
AddedRunningSum
This is how it is used in the main query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZI9DsMgDIWvUmUOyDYY4wP0FFHGrp2qnr8h/MRUGcBP8Mk8G2/bgkSyrAscS0XZi0zq+X29P2HZ14MEpUayqnrKkzIkQU+ZUH0OVlgMc8Oc8NgNEGkAyCYYJEUeCPEVbBbRhiCACSfyoMo0IgCjV5zUxKXRg5Rz8MiTmtAYelZkQn8WdymL9hK0GlPjr5Yg2htBBIk80CwNCg3sy35g8ZNqcrikJVLpYDs3ZDWLlVGRuzfadTEaYsTe0CotUabAGcTdMLE84RiKHwauo+PAo/y7OYHU5/JU437/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DayDiff = _t, prov = _t, paid = _t, Total_paid = _t, event = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DayDiff", Int64.Type}, {"prov", Int64.Type}, {"paid", type number}, {"Total_paid", type number}, {"event", type text}}),
TableType = Value.Type(Table.AddColumn(#"Changed Type", "Running Sum", each null, type number)),
#"Grouped Rows" = Table.Group(#"Changed Type", {"event"}, {{"AllData", AddedRunningSum, TableType}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"DayDiff", "prov", "paid", "Total_paid", "Running Sum"}, {"DayDiff", "prov", "paid", "Total_paid", "Running Sum"})
in
#"Expanded AllData"
This is the result:
Is this what you are after?
Kind regards,
JB
Hi- @Anonymous Nearly! I need to have a running total of paid, prov and total paid in three new columns The date order by event is correct though.
Thanks in advance
Hi @Anonymous
Try this one- i am just repeating the cumulative step 3 times - this should work if I get the referensing right. Sorry I do not have access to pbi at the moment. Please let.me know if this does not work.
(MyTable as table) as table => let Source = Table.Buffer(MyTable), Add1=Table.AddColumn(Source, "Running Sum (paid)", each null, type number), Add2=Table.AddColumn(Add1, "Running Sum (prov)", each null, type number), Add3=Table.AddColumn(Add2, "Running Sum (total paid)", each null, type number), TableType = Value.Type(Add3), Sorted = Table.Sort(Source,{{"DayDiff", Order.Ascending}}), Cumulative1 = List.Skip(List.Accumulate(Sorted[paid],{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + cost})), Cumulative2 = List.Skip(List.Accumulate(Sorted[prov],{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + cost})), Cumulative3 = List.Skip(List.Accumulate(Sorted[total paid],{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + cost})), AddedRunningSum = Table.FromColumns(Table.ToColumns(Sorted)&{Cumulative1}&{Cumulative2}&{Cumulative3}, TableType) in AddedRunningSum
Kind regards,
JB
Great thank you!
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 |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |