Measure help

[Sperling]

Hi,

I'm having issues with an out of stock measure.

Currently I have an qty. available to sell measure that is made up of two other measures.

One is an qty. in stock measure - shows how much we have in stock at the moment.

The other is open sales - essentially items sold that aren't yet removed from the qty. in stock measure.

These three measures work fine.

Using the qty. available to sell measure I want to calculate the out of stock share.

To describe the model a bit more, the relevant tables I have for this are a fact table for inventory, a fact table for sales, and a dimension for items.

A unique item exists in several "company" identifiers/names, which is the core of my issue.

So far I've attempted to make a calculated column in the item dimension, where I give the item a value of 1 if it is out of stock (qty available to sell below 1) and 0 if it is in stock (qty available to sell above 0).

However, due to the way our inventory is set up, some items will have +1 in one company and -1 in another, adding up to 0 qty. available to sell. My calculated column therefore thinks the item is available to sell in one company and not available to sell in another, rather than summing the value of the qty. available to sell and realizing its 0.

Item number		Qty. available to sell		OOS calculated column		CompanyId
ExampleName1		-1		1		101
ExampleName1		+1		0		102

Essentially I want my final measure to ignore the fact that the item exists in several companies, so I can say that this item is 100% out of stock, since there is 0 qty. available to sell.

Let me know if I should add more context.

[Sperling]

My solution is going to be part of a cube, so summarize isn't really a solution for me cause of relationship issues.

I've gotten a bit further than last, but I've got issues with the sum of the measure I'm making. I've taken the measure I divide with to get the OOS share out as it doesn't have issues, so now I just want to summarize how many unique items are out of stock.

OOScount =

VAR QtyAtS =

    SUMX(

        DISTINCT(DimItem[Item Number]),

        [Quantity available to Sell]

    )

VAR OOSstatus =

    IF(

        CALCULATE(

            QtyAtS,

                ALLEXCEPT(

                'DimItem',

                'DimItem'[Item Number]

            )

        ) <= 0,

        1,

        0

    )

RETURN

COUNTROWS(

    FILTER(

        DISTINCT(

           DimItem[Item Number]

        ),

       OOSstatus = 1

    )

)

This gives me the right value on item level, but the sum is 0 which makes the out of stock share fail.

[scott_henderson]

Not 100% sure here, but my gut is telling me if you can get your above as a calculated column in the table, then do a simple sum on that column ?  Is that an option ?

[Sperling]

Appreciate the input, but since items exist in multiple companies, the sum of the calculated column is more than 1 if the stock status is out of stock in several. So if it for example is out of stock in 6 companies, the sum will be 6 rather than 1.

[Sperling · Accepted solution]

In trying to debug it by splitting it more up, it turned out to work.
Never tried a measure not working by using VAR.

So in my solution I just divided the DAX presented into 2 individual measures:

Out of Stock status =

VAR QtyAtS =

    SUMX(

        DISTINCT(DimItem[Item Number]),

        [Quantity available to Sell]

    )

RETURN

    IF(

        CALCULATE(

            QtyAtS

        ) <= 0,

        1,

        0

    )

Out of Stock count =

    COUNTROWS(

        FILTER(

            DISTINCT(

                DimItem[Item Number]

            ),

            [Out of Stock status] = 1

        )

    )


I then divided the Out of Stock count measure with a distinct count of Item Numbers and it works - a bit confused but at least it works.

[scott_henderson]

I'd start by using summarize to summarize your stock by company and product then run your calculations on that table.

https://learn.microsoft.com/en-us/dax/summarize-function-dax

[Sperling]

Bump (is this even allowed)