Creating sub columns in power dashboard

[Anonymous]

Hello Community,

I am trying to build a table/matrix in power bi dashboard which looks as below, anyone who can help me with that?

[Stachu]

I think this functionality is still in the ideas:
https://ideas.powerbi.com/forums/265200/suggestions/36038050
you could achive something similar with Matrix visual if the columns you show would look like this

Sales Person
Sales Person
Product Sold	Jeans
Product Sold	Shirts
Product Sold	Jackets
Target

I guess the second column is populated for all, but you could create a new column just for this purpose - not an elegant solution, but may work

[Anonymous]

Hello Stachu,

I tried several things with matrix, but it couldn't give the desired result as mentioned above. The actual table looks like this;

[Stachu]

you need to modify the source then, right now there is no indication at all which elements are a sub group of what
I thuoght you actually have a proper hierarchy there, then Unpivot in the Query editor would solve this

with this format I think you can do following:
1) unpivot all so the labels are in rows, filter out Total Products sold:

2) add custom column that would say "Total Products sold" for Jeans, Shirts and Jackets and else whatever is in the original column
3) add custom column that would just be  Jeans, Shirts and Jackets and else empty
4) put the column from 2 &3 in the columns for the matrix visual, and the Value column in Values

[Anonymous]

Tried unpivot earlier as well, but couldn't get the exact matrix that I am looking for. if I populate then it gives more details about the total product sales and product types etc but thats not my requirement.

sample data

[Stachu · Accepted solution]

that's why I wrote that you have to filter out Total Products sold in step 1), otherwise it will be duplicated
create a new query in the Query Editor, go to advanced editor and paste this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kpMzlbSUTI0NTAwANJGFiCOAVgESBiDGBYgqVidaCXfzOxUkBoDiFpjI5gyQxALxDAygqkNygQbawQ1FqwKbJohiABiiKmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Sales Person" = _t, Sales = _t, #"Total Products sold" = _t, Jeans = _t, Shirts = _t, Jackets = _t, #"Target Sales" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Person", type text}, {"Sales", Int64.Type}, {"Total Products sold", Int64.Type}, {"Jeans", Int64.Type}, {"Shirts", Int64.Type}, {"Jackets", Int64.Type}, {"Target Sales", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Sales Person"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] <> "Total Products sold")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "L1", each if ([Attribute] = "Jeans" or [Attribute] = "Shirts" or [Attribute] = "Jackets") then "Total Product sold" else [Attribute]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "L2", each if [L1] = "Total Product sold" then [Attribute] else ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute"})
in
    #"Removed Columns"

it will produce table like this

which you can turn into requested visual