Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Creating sub columns in power dashboard

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?

 

 

Show and Tell
  • Stachu's avatar
    Stachu
    7 years ago

    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

5 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    7 years ago

    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 SoldJeans
    Product SoldShirts
    Product SoldJackets
    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Stachu
        Community Champion
        7 years ago

        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