How to show average and total together in report view

[im_newbie_123]

I have to show average = row data total/count of  month number.

Total = column data sum

Expected output:

	M1	M2	M3	M4	M5	M6	M7	M8	M9	M10	M11	M12	Average*
John	72	71	51	52	51	49	49	53	52				56
Mary	97	93	94	94	95	95	93	97	95				95
Sam	252	251	249	251	254	259	262	260	266				256
Hans	301	300	299	299	296	294	293	301	295				298
Samuel	160	153	152	153	159	160	160	157	159				157
Henry	683	689	686	697	717	707	714	717	707				702
Zaltan	48	47	47	51	48	49	49	51	54				49
Messi	6	20	40	42	43	46	48	49	52				38
Rolando	13	13	14	15	15	15	14	15	15				14
Total*	1632	1637	1632	1654	1678	1674	1682	1700	1695				1665

[mwegener]

Hi @im_newbie_123 

did you solve your problem?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Icey · Accepted solution]

Hi @im_newbie_123 ,

Try this:

Attach my PBIX file and the complete code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZHBCsIwDIZfRXbeYW3Tdn0DEbzozeGh4EBhbuD04Nvb/Fur3YTtI03Dnz9N0xS74doXZWElQwRoQMaIXIRW8SJ8G/znsin2/vEOB2cZXOIoQSeoVKKXAkd/D7GEskRPiX5zrAlExqDGVKBZCm19P4aDqgSIKud+aEDowdBUKf9aerVdOAr0EhhdwGGM3fd2rrEpn7tqezyQqRXoQLZi8CBWgNUU0yKTSZ189/S8LaoZNmJaVJ1vC2uk1bbacbxxbx6cfRPAkxH7I5NJrdd9GDrfXwaeVSUQRs+R574a5w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, M1 = _t, M2 = _t, M3 = _t, M4 = _t, M5 = _t, M6 = _t, M7 = _t, M8 = _t, M9 = _t, M10 = _t, M11 = _t, M12 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"M1", Int64.Type}, {"M2", Int64.Type}, {"M3", Int64.Type}, {"M4", Int64.Type}, {"M5", Int64.Type}, {"M6", Int64.Type}, {"M7", Int64.Type}, {"M8", Int64.Type}, {"M9", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Month"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Month", "Month - Copy"),
    #"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "Month - Copy", Splitter.SplitTextByPositions({0, 1}, false), {"Month - Copy.1", "Month - Copy.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Name", type text}, {"Month - Copy.1", type text}, {"Month - Copy.2", Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Month - Copy.1"})

in
    #"Removed Columns1"

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mwegener]

Hi @im_newbie_123 ,

If you define measures for M1..M12.

Can you set the measures M1..M12 + Average in the column.

Totals on rows is a matrix standard.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.