SKU count ver. SKU bellow minimum

[SebaG]

Hi All

I need to count how many of my SKU's are bellow minimum.

Firstly I counted my SKU's = DISTINCTCOUNT('weekly stock'[Item Code])

Than I counted SKU's with 0 inventory = CALCULATE(DISTINCTCOUNT('weekly stock'[Item Code]); FILTER('weekly stock'; 'weekly stock'[Volume]=0))

Now I want to see how many SKU's are bellow minimum. And the minimum will be based on box quantity and item class. Depends on item class, check full box quantity and multiply by factor and set a minimum, and than check current stock, and count SKU where current stock is less then minimum. Factor will be different for different item class.

Example; If item class is B2, than minimum quantity is 80% of a full box quantity. Item "0020" is B2, and full box is 100 pieces, the minimum will be 80 pieces, current stock is 378 pieces, so is above minimum.

If item class ic C3, tahn minimum quantity is 20% of a full box quantity. Item "0009" is C3, and full box is 50 pieces, the minimum will be 10 pices, current stock is 5 pieces, so is bellow minimum

Factors for item classes are:

ItemClass	Factor
A1	1000%
A2	700%
A3	500%
A4	300%
B1	100%
B2	80%
B3	60%
B4	50%
C1	40%
C2	30%
C3	20%
C4	10%

Weekly inventory table looks like that:

ItemCode	StockQuantity	ItemClass	CaseQuantity
0001	2	C1	50
0002	8	B4	50
0003	3	B3	50
0004	19	B4	50
0005	9	C1	36
0006	53	C1	36
0007	25	C1	36
0008	0	C2	50
0009	5	C3	50
0010	63	C4	50
0011	0	C4	100
0012	253	C1	100
0013	650	B4	24
0014	1238	B3	24
0015	1348	B4	100
0016	576	C2	100
0017	0	C4	100
0018	0	C4	100
0019	197	B4	48
0020	378	B2	100
0021	1991	B1	100
0022	915	B3	100
0023	494	B4	48
0024	0	C3	48
0025	0	C4	100
0026	0	C4	100
0027	0	C4	12
0028	0	C4	100
0029	0	C4	100
0030	115	C4	12
0031	163	C3	6
0032	370	C2	6

If anyone nows the solution please explain.

Thank you, Sebastian

[Anonymous · Accepted solution]

Measure 1: Number of Items 

NoOfItems = DISTINCTCOUNT(WeeklyInventory[ItemCode])

Measure 2: Number of items with zero stock

ZeroStockItemCount =
CALCULATE (
    DISTINCTCOUNT ( WeeklyInventory[ItemCode] ),
    WeeklyInventory[StockQuantity] = 0
)

Add a Calculated Column to your inventory table

MinimumStock = WeeklyInventory[CaseQuantity]*RELATED(Factors[Factor])

Remember to create a relationship between as follows...

Factors[ItemClass] -> WeeklyInventory[ItemClass]

Filter Direction: One to Many

Measure 3: Items with inventory less than minimum stock level.

LessThanMinimumStockItemCount =
COUNTROWS (
    FILTER (
        WeeklyInventory,
        WeeklyInventory[StockQuantity] < WeeklyInventory[MinimumStock]
    )
)

[SebaG]

Thank You, now it so easy 🙂

Step by step, great help.