Forum Discussion

Sansudhi8's avatar
Sansudhi8
Helper III
7 years ago
Solved

Matrix_ Help

Hi, 

 

I have below given sample data file. I just want to summarize those as given below. could you please help me to do it power bi desktop. 

 

Thank you.

 

 

  • in order to do that the rows & column items have to be in rows in the table

    you can achieve that in 2 ways:

    1) unpivoting your original table in query editor, e.g. like this

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBLCoUwDAXQvWSs0KT1N9T3/LuD4v63obF6Rw2UQMMhNyRG6qkgcdyWju93f+pGi3YDnUWkAUASYPGo4hP6AfmEfKUdfsbwQ/4g4SVBzVeUjCCVNWUCqfPrzgCNve4C1FpBK0iXD9o+wM4O2oHYCjpAJHuX8wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Client = _t, Month = _t, #"Total Charge" = _t, #"Total Payment" = _t, #"Total Balance" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Month", type date}, {"Total Charge", Int64.Type}, {"Total Payment", Int64.Type}, {"Total Balance", Int64.Type}}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Client", "Month"}, "Total", "Value")
    in
        #"Unpivoted Columns"

    then you have one measure which is just 

    Measure= SUM(Table[Value])

    2) create a new table with a row for each column you wanna sum

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvScxRcM5ILEpPVYrVgQkEJFbmpuaVIIk4JeYk5iUD1cQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Total = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Total", type text}})
    in
        #"Changed Type"


    and then use this measure

    Measure 2 = 
    SWITCH(
    SELECTEDVALUE(RowsTable[Total]),
    "Total Balance", SUM('Table'[Total Balance]),
    "Total Charge", SUM('Table'[Total Charge]),
    "Total Payment", SUM('Table'[Total Payment]),
    BLANK()
    )

1 Reply

  • Stachu's avatar
    Stachu
    Community Champion

    in order to do that the rows & column items have to be in rows in the table

    you can achieve that in 2 ways:

    1) unpivoting your original table in query editor, e.g. like this

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBLCoUwDAXQvWSs0KT1N9T3/LuD4v63obF6Rw2UQMMhNyRG6qkgcdyWju93f+pGi3YDnUWkAUASYPGo4hP6AfmEfKUdfsbwQ/4g4SVBzVeUjCCVNWUCqfPrzgCNve4C1FpBK0iXD9o+wM4O2oHYCjpAJHuX8wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Client = _t, Month = _t, #"Total Charge" = _t, #"Total Payment" = _t, #"Total Balance" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Month", type date}, {"Total Charge", Int64.Type}, {"Total Payment", Int64.Type}, {"Total Balance", Int64.Type}}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Client", "Month"}, "Total", "Value")
    in
        #"Unpivoted Columns"

    then you have one measure which is just 

    Measure= SUM(Table[Value])

    2) create a new table with a row for each column you wanna sum

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvScxRcM5ILEpPVYrVgQkEJFbmpuaVIIk4JeYk5iUD1cQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Total = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Total", type text}})
    in
        #"Changed Type"


    and then use this measure

    Measure 2 = 
    SWITCH(
    SELECTEDVALUE(RowsTable[Total]),
    "Total Balance", SUM('Table'[Total Balance]),
    "Total Charge", SUM('Table'[Total Charge]),
    "Total Payment", SUM('Table'[Total Payment]),
    BLANK()
    )