What is the data structure to create a matrix table?

[EugenioProlog]

I want to create a dashboard for inventory control. I need to manage the stock of macro items (kits) and sub-items (components of the kits). I want to create a matrix table where I have the items (kits) in the rows and the stock value, monthly consumption, and replenishment lead time in the columns. In this matrix table, I want to be able to expand the rows (kits) into rows for the sub-items.

How would I structure the data on my tables to create this type of matrix table?

Thank you.

(Example Image of the kind of drill-down I want, please ignore the names)

[danextian]

Hi @EugenioProlog 

There isn’t a one-size-fits-all answer to this. Even with a very flat dataset, that layout can be achieved as long as the necessary columns exist—similar to how it works in Excel pivot tables. However, from a data modeling perspective, date attributes and product attributes should reside in separate dimension tables and be related to the fact table through a product key/ID and a date ID or date column. When a measure or aggregation is added to a visual alongside columns from these dimension tables, Power BI automatically displays only the rows where records exist for the corresponding combinations of dimension attributes.

[FBergamaschi · Accepted solution]

Hi @EugenioProlog,

you should have:

one fact table with product id, day (or YearMonth), qty and value 

one calendar table at the day or month granularity, depending on your needs (Primary Key Date or YearMonth)

one product table (PK ProductId, leadtime, other attributes, SubitemId)

one subitem table (PK SubitemId, attributes, KitId))

one kit table (PK KitId and attributes)

connect all dimentions to the fact table one to many

then you need a DAX measure to implement your logic (measures)

For example, the data you are showing refer to February, is that the value at the end of the month?

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI