Category Managment Availability

[Anonymous]

Hello there,

I've come a bit to my limit with DAX formulas...

I want to calculate the availability of products depending on the category and size each store has assigned.

I already had an availabity formula:

# Products = COUNT( DimAssortment[Product ID] )

Stock Quantity > 0 = 
CALCULATE(
	COUNTROWS(
		FILTER(
			FactStock;
			FactStock[Quantity] > 0
		)
	);
	FILTER(
		DimAssortment;
		DimAssortment[Assortment] IN VALUES( DimAssortment[Assortment] )
	)
)

Availability = 
DIVIDE(
	[Stock Quantity > 0];
	[# Products] * COUNT( DimStore[Outlet ID] )
)

And have adapted them for this scenario:

# Products CatMan = 
COUNTROWS(
	FILTER(
		CROSSJOIN( DimStore ; DimAssortment );
		DimAssortment[Assortment] IN VALUES( DimCatMan[Assortment] ) &&
		DimStore[Outlet ID] IN VALUES( DimCatMan[Outlet ID] )
	)
)

Stock Quantity > 0 CatMan = 
CALCULATE(
	COUNTROWS(
		FILTER(
			FactStock;
			FactStock[Quantity] > 0
		)
	);
	FILTER(
        CROSSJOIN( DimStore ; DimAssortment );
        DimAssortment[Assortment] IN VALUES( DimCatMan[Assortment] ) &&
    	DimStore[Outlet ID] IN VALUES( DimCatMan[Outlet ID] )
    )
)

Availability CatMan = 
DIVIDE(
	[Stock Quantity > 0];
	[# Products CatMan]
)

But with all the assigning of product groups and size it's getting a bit complicated for me...

I've managed to get the 'Availability CatMan' calculated per product and store right but the totals aren't correct...

The problem is that 'CROSSJOIN' is not giving me the right table. I have to manage to filter the table to just the right store, product group and assortment combination.

I've uploaded the Power BI model I'm testing everything to google drive.

Let's see if somebody can help me :D:D

Excel Data:

Download Excel Data

Power BI:

Download Power BI model

Thanks a lot,

Oliver

[v-chuncz-msft]

@Anonymous,

Check if the following post helps.

https://community.powerbi.com/t5/Desktop/Sum-of-values-in-a-measure-with-divide-measure/m-p/296768#M130947