Forum Discussion
Filter sum column by category
I want to create a slicer of categories so that I can see the evolution of the portfolio by period. Any idea how to do it for the example below?
| Period | Portfolio (A+B+C) | Category A | Category B | Category C |
| jan-19 | 1406 | 490 | 455 | 461 |
| feb-19 | 1297 | 485 | 361 | 451 |
| mrt-19 | 941 | 339 | 280 | 322 |
| apr-19 | 1030 | 460 | 321 | 249 |
| mei-19 | 915 | 190 | 316 | 409 |
| jun-19 | 569 | 164 | 293 | 112 |
| jul-19 | 929 | 192 | 368 | 369 |
| aug-19 | 1429 | 458 | 471 | 500 |
| sep-19 | 1155 | 405 | 300 | 450 |
| okt-19 | 778 | 123 | 290 | 365 |
| nov-19 | 951 | 415 | 344 | 192 |
| dec-19 | 886 | 207 | 404 | 275 |
Go into Power Query, get rid of the sum column, then unpivot the individual category columns so you have:
period - category - value
jan-19 - a - 490
jan-19 - b - 455
jan-19 - c - 461
feb-19 - a - 485
etc - the category column can then just be dropped into a slicer as you want
Hi Anonymous ,
1. Unpivot the table.
M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RZC5bgMxDET/ZWsbIEXqYGkgSOEijUvDRRIsgiCHDSPO91sz2k0aSiCfRjM8HieN7f75e9pM6lL64SGoOaMWnU4bMo/zC5gUFf2GqfUpyMF8XX+2Gr0RjrYZ7qlBzFJaZXaXK2TE+EkZU/DJY8jM74uM4g+lG1M6k1hl9jc4zgWgFsf7MNw1/SOfUElEItFvY/1T2d3emJuMZ0y9wkwWWZnDfKHu2Icwt4wNDeb8seSutXFDRje0XfIq83T+hZvMjTGZuS/OFuRhfu2N1hA1CdcsTFa7yukO", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Period = _t, #"Portfolio (A+B+C)" = _t, #"Category A" = _t, #"Category B" = _t, #"Category C" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type text}, {"Portfolio (A+B+C)", Int64.Type}, {"Category A", Int64.Type}, {"Category B", Int64.Type}, {"Category C", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Portfolio (A+B+C)"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Period"}, "Attribute", "Value") in #"Unpivoted Other Columns"2. Then we can see the evolution of the portfolio by period.
For more details, please check the pbix as attached.
3 Replies
Go into Power Query, get rid of the sum column, then unpivot the individual category columns so you have:
period - category - value
jan-19 - a - 490
jan-19 - b - 455
jan-19 - c - 461
feb-19 - a - 485
etc - the category column can then just be dropped into a slicer as you want
Hi Anonymous ,
1. Unpivot the table.
M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RZC5bgMxDET/ZWsbIEXqYGkgSOEijUvDRRIsgiCHDSPO91sz2k0aSiCfRjM8HieN7f75e9pM6lL64SGoOaMWnU4bMo/zC5gUFf2GqfUpyMF8XX+2Gr0RjrYZ7qlBzFJaZXaXK2TE+EkZU/DJY8jM74uM4g+lG1M6k1hl9jc4zgWgFsf7MNw1/SOfUElEItFvY/1T2d3emJuMZ0y9wkwWWZnDfKHu2Icwt4wNDeb8seSutXFDRje0XfIq83T+hZvMjTGZuS/OFuRhfu2N1hA1CdcsTFa7yukO", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Period = _t, #"Portfolio (A+B+C)" = _t, #"Category A" = _t, #"Category B" = _t, #"Category C" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type text}, {"Portfolio (A+B+C)", Int64.Type}, {"Category A", Int64.Type}, {"Category B", Int64.Type}, {"Category C", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Portfolio (A+B+C)"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Period"}, "Attribute", "Value") in #"Unpivoted Other Columns"2. Then we can see the evolution of the portfolio by period.
For more details, please check the pbix as attached.