Forum Discussion

Sperling's avatar
Sperling
Advocate II
3 years ago
Solved

Measure help

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...
Need Help
  • Sperling's avatar
    Sperling
    3 years ago

    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.
Sperling's avatar
Sperling
Advocate II
3 years ago

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's avatar
    scott_henderson
    Helper II
    3 years ago

    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's avatar
      Sperling
      Advocate II
      3 years ago

      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's avatar
        Sperling
        Advocate II
        3 years ago

        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.