Forum Discussion

RT3308's avatar
RT3308
Helper I
6 years ago
Solved

Matrix Row Grouping OFF Column Grouping ON

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 ...
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    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.

RT3308's avatar
RT3308
Helper I
6 years ago

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's avatar
v-juanli-msft
Community Support
6 years ago

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's avatar
    RT3308
    Helper I
    6 years ago

    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.