Forum Discussion

Carlo1975's avatar
Carlo1975
Icon for Helper I rankHelper I
5 years ago
Solved

Matrix table with sum

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 ...
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

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

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

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
5 years ago

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