topic Re: Creating Cateogry Product and Multiple Sub-Categroies in DAX Commands and Tips

Creating Cateogry Product and Multiple Sub-Categroies

CuriousGuy001 — Wed, 22 May 2024 13:59:56 GMT

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.

Re: Creating Cateogry Product and Multiple Sub-Categroies

some_bih — Wed, 22 May 2024 14:36:30 GMT

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.

Re: Creating Cateogry Product and Multiple Sub-Categroies

CuriousGuy001 — Wed, 22 May 2024 15:41:27 GMT

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.

Re: Creating Cateogry Product and Multiple Sub-Categroies

AntrikshSharma — Wed, 22 May 2024 16:42:45 GMT

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