Matrix Row Grouping OFF Column Grouping ON

[RT3308]

Hello... please help (PowerBIDesktop & OnPremise PBIRS).

Is there a matrix where RowGrouping is OFF and ColumnGrouping is ON?  I don't need the Row fields to be grouping... but I need the Column field (e.g. Months) to be grouped.  Basically, I've got dimensions on Rows that I want to just repeat on each row and therefore treated as individual rows (combination of different fields).  I need them to be sorted by Columns-Values Totals... so having the Row Grouping will prohibit it from sorting the way I want it to be. 

Thank you sooo much!

[parry2k]

@RT3308 not sure what you mean row grouping off,  you don't want to show row group total , is that what you are asking for? Please provide more context.

[RT3308]

Hello @parry2k , thanks for the reply.

I can turn off the group subtotals - but it's not what I need. 

Matrix Rows Fields: Region, Class, Description

Matrix Column Fields: Month-Year

Matrix Values: Sum of Score

Basically I need this

 

to turn into this

[v-juanli-msft]

Hi @RT3308 

It is possible with a workaround, but it need some transformation in Edit queries.

Add a custom column->remove date column->pivot columns->add total column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc8xCsAgDIXhu2QWqkkQOta5dOwi3v8aTQdbJTFDUPiGn1crHBAgyd3vu6UNY9rlixFamBV/JVZKg2albCrKXXOXFQ7ZqJA8ZAtJ7pybGs1mR7PZUTWLvRO1oqvk6pBN+dPl1uJtLd7WstzaHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [region = _t, class = _t, des = _t, date = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"region", type text}, {"class", Int64.Type}, {"des", type text}, {"date", type date}, {"value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine({Text.Start(Date.MonthName([date]), 3), Text.From(Date.Year([date]), "en-US")}, "-")),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "month-year"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"date"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"month-year"]), "month-year", "value"),
    #"Added Custom1" = Table.AddColumn(#"Pivoted Column", "total", each [#"Jan-2019"]+[#"Feb-2019"]+[#"Mar-2019"]+[#"Apr-2019"])
in
    #"Added Custom1"

Code in Advanced editor

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RT3308]

Wow... thanks for that @v-juanli-msft  That would have been perfect if I can use pivot table.  My data source is an SSAS Tabular Model (Dimensions-FactTables) wouldn't work for that approach. 😞 This would mean that I'd need to use Table instead of a Matrix. Hmmm... this is so challenging.  I wonder if there are other workarounds. 

[parry2k · Accepted solution]

@RT3308 unfortunaltey you cannot achieve this to repeat the value on each row for main group. May be you want to add this on ideas forum or vote if Idea already exists.