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.

parry2k's avatar
parry2k
Super User
6 years ago

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'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

  • parry2k's avatar
    parry2k
    Super User
    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.

  • 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.