Creating Cateogry Product and Multiple Sub-Categroies

[CuriousGuy001]

Hi Everyone,

I have been trying to figure out a way to create a Matrix vizualizations for a report, I have the on the column for Sales rep. and row for product category and what I want is to insert ONE measure that contain 4 subcategory and within the 4 subcategories can expand into more detail of items that have been sold by each Sales rep. The values will the Total count that the sales rep sold.

The problem that I get whenever I drag 4 data (each column) into the rows, it creates a subcategory of a subcategory of a subcategory, so I was wondering if there a way to fix this in a matrix.

 

[AntrikshSharma · Accepted solution]

@CuriousGuy001 The right way to store this type of data is to have 3 columns Category > Subcategory >  Name by unpivoting other columns

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs3JLElVcCrNS8lJVdJRciwoANNORfnJ+TmZQJZzYnFxYlkikOWWWZyhFKsD0xOQk1iSmVeai9DsX5SYlw5ihOTnJpbkpxaDDEpNLQHRzkWJScVKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Fruits = _t, Vegetables = _t, Roots = _t, Seafood = _t]),
    ChangedType = Table.TransformColumnTypes ( 
        Source,
        {
            {"Category", type text}, 
            {"Fruits", type text}, 
            {"Vegetables", type text}, 
            {"Roots", type text}, 
            {"Seafood", type text}
        }
    ),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns (
        ChangedType, 
        {"Category"}, 
        "Subcategory", "Name"
    )
in
    UnpivotedOtherColumns

use the above code to generate the table.

Then you can create a Matrix like the following one and you won't have repeated unexisting combinations

[some_bih]

Hi @CuriousGuy001 matrix usually reflect what is your model (relationships and measures).

Check / change your model or measure definition to see how it affects matrix results.

Without model and simple data it is hard to spot your issue.

[CuriousGuy001]

Hi there,

Thank you for your comment! I will post a diagram to what I am looking for. Let me know if that help to understand my questions.