Filter sum column by category

[Anonymous]

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

[jthomson · Accepted solution]

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

[v-frfei-msft · Accepted solution]

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.

[Anonymous]

As suggested, remove the Portfolio column and unpivot the table on Period, then use the Category column in the slicer.

[jthomson · Accepted solution]

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