Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
RT3308
Helper I
Helper I

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

5 REPLIES 5
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

sample1.png 

to turn into this

sample2.png

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

Capture19.2.JPGCapture20.JPGCapture21.JPG

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.

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. 

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.