Matrix table with sum

[Carlo1975]

Hi, I have a problem and I don't know how to overcome it. I don't know if the solution is Dax or power query. I'm sorry.

I try to explain.

I have a table like this. With 2 clusters columns, one column with the quarters, and one with an amount:

Cluster1	Cluster2	Quarter	Amount
Buy	01. > 5.0ml	3	150
Buy	02. 2.5 - 5.0ml	2	250
Buy	01. > 5.0ml	1	150
Buy	01. > 5.0ml	2	150
Buy	03. 1.0 - 2.5ml	1	330
Buy	03. 1.0 - 2.5ml	3	330
Buy	02. 2.5 - 5.0ml	1	140
Buy	02. 2.5 - 5.0ml	3	140
Buy	01. > 5.0ml	3	550
Buy	03. 1.0 - 2.5ml	2	180
Sell	02. 2.5 - 5.0ml	2	256
Sell	03. 1.0 - 2.5ml	2	987
Sell	01. > 5.0ml	2	6,321
Sell	01. > 5.0ml	1	6,321
Sell	02. 2.5 - 5.0ml	1	1,211
Sell	03. 1.0 - 2.5ml	1	2,586
Sell	01. > 5.0ml	1	1,211
Sell	01. > 5.0ml	3	6,321
Sell	02. 2.5 - 5.0ml	3	1,211
Sell	03. 1.0 - 2.5ml	3	2,586

If I try to do a matrix table I don't have any problem

up to here everything is quite simple.

The problem is that, for each row, I have to sum the previous value with the following. And so, if you look at first row, you have for the first quarter 150, the second 150 and on the last one 700:

In my new Matrix Table I have to do this: On the first quarter I will have 150, on the second quarter 300 (because I have to sum the 150 on the first quarter, to the second). In the third quarter, I will have 1000 because I will sum 150 (first quarter), 150 (second quarter) and 700 (last one). The new first row will be:

This one, for all the row and all the quarters.

thank you.

[AlB]

Hi @Carlo1975 

Certainly best to do this in DAX. If you want it in PQ, place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdIxDsMgDAXQqyBmatkmULrmCh3TjNnomKG3L1KrSATJhjYSS4an728vi533l3UWCcxjR/SbCYDPXH758oz0UUC7ukNgMAzBXA6ANYBroI1AgxFaQc1wEjwYAixDlFH6IngvA2qNJ6CpUS9hkgF9kZPSoioEpUV9DekD3Lecfz2mWAnjGW7pWgmj1xSdZ1IEeZmtMHgN5JhIrkEG2IUU/5qhjTB6Tx0tyEBHCzLwbWF9Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cluster1 = _t, Cluster2 = _t, Quarter = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cluster1", type text}, {"Cluster2", type text}, {"Quarter", Int64.Type}, {"Amount", type number}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Cluster1", "Cluster2", "Quarter"}, {{"Amount", each List.Sum([Amount]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Sum(Table.SelectRows(#"Grouped Rows", (inner)=>Record.SelectFields(_, {"Cluster1", "Cluster2"}) = Record.SelectFields(inner, {"Cluster1", "Cluster2"}) and (inner)[Quarter]<=[Quarter])[Amount])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Amount"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Cluster1", Order.Ascending}, {"Cluster2", Order.Ascending}, {"Quarter", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows", {{"Quarter", type text}}), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{"Quarter", type text}})[Quarter]), "Quarter", "Custom", List.Sum)
in
    #"Pivoted Column"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[CNENFRNL · Accepted solution]

Hi, @Carlo1975 , you might want to use this measure,

Accum = 
VAR __qtr = MAX ( Table1[Quarter] )
RETURN
    CALCULATE ( SUM ( Table1[Amount] ), Table1[Quarter] <= __qtr )