Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to 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.
@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
to turn into this
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
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.
User | Count |
---|---|
120 | |
69 | |
68 | |
57 | |
50 |
User | Count |
---|---|
166 | |
82 | |
68 | |
65 | |
53 |