Cumulative with filters

[Anonymous]

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

[Anonymous · Accepted 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

[Anonymous]

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

[Anonymous]

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 

[Anonymous · Accepted 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

[Anonymous · Accepted solution]

Great thank you!