Displaying size range for items with no data

[mamal]

Hi,

I am trying to find the best way to present data of product availability as follows:

1. We have a table of available stock in different sizes but not always all sizes are available (in which case there would be no entry for them); this table also contains other columns such as availability date.

Product	Size	Available date	Qty
A	S	1.1.2025	5
A	S	1.1.2026	10
A	XS	1.1.2025	4
A	L	1.1.2025	7
B	M	1.1.2024	8
B	M	1.1.2025	7
B	S	1.1.2024	2
B	L	1.1.2024	9
C	XS	1.1.2025	8
C	M	1.1.2024	4
C	L	1.1.2025	9
C	L	1.1.2026	10

2. I also have a table lookup with size grid for different products:

Product	Size
A	XS
A	S
A	M
A	L
B	S
B	M
B	L
B	XL
C	XS
C	S
C	M
C	L

What I would like to achieve is a table, in which this data is consolidated and the size grids are displayed for each respective product by either highlighting cells within grid or by displaying 0 (but due to the size of the data table, I hope to avoid creating "dummy" rows of data for the missing sizes).

So the end result should look something like this:

or

I would appreciate any ideas!

[johnbasha33]

@mamal 

To achieve the desired result in Power BI, you can follow these steps:

1. Load both tables (available stock and size grid lookup) into Power BI.
2. Create a relationship between the two tables based on the "Product" column.
3. Create a new calculated column in the size grid lookup table to concatenate the "Product" and "Size" columns. This will be used for lookup purposes.
4. Create a new calculated column in the available stock table to calculate the total quantity for each product and size combination. Use a DAX formula like this:

```dax
TotalQty = CALCULATE(SUM('Available Stock'[Qty]), ALLEXCEPT('Available Stock', 'Available Stock'[Product], 'Available Stock'[Size]))
```

5. Use a matrix or table visual in Power BI to display the data. Use the "Product" column from the size grid lookup table as rows and the "Size" column from the size grid lookup table as columns.
6. Use the "TotalQty" calculated column as the values in the matrix or table visual.
7. Format the visual to display 0 instead of blank for missing values. You can do this by going to the "Format" pane, expanding the "Values" section, and selecting "Show items with no data" and choosing "Show items with no data as 0".

This approach will dynamically display the available stock quantities for each product and size combination, without the need for dummy rows for missing sizes. You can customize the visual further to highlight cells or apply conditional formatting as needed.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!