Forum Discussion

AV_04's avatar
AV_04
Helper I
3 years ago
Solved

DAX Help

Hi team,   Could anyone help me with the below dax requirement? I have a table with product, sub-product, Quantity Sold and Stock available.  Product                  Sub-Product          Bou...
Need Help
  • Samarth_18's avatar
    Samarth_18
    3 years ago

    AV_04 , Okay then please create a column as below:-

    _no_stocks_left = 
VAR cust_bought =
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[Product] = EARLIER ( 'Table'[Product] )
                && 'Table'[Sub-Product] = EARLIER ( 'Table'[Sub-Product] )
        )
    )
VAR stock_available =
    CALCULATE (
        MAX ( 'Table'[Stocks Available] ),
        FILTER (
            'Table',
            'Table'[Product] = EARLIER ( 'Table'[Product] )
                && 'Table'[Sub-Product] = EARLIER ( 'Table'[Sub-Product] )
        )
    )
VAR _result = stock_available - cust_bought
RETURN
    IF ( _result = 0, 1,0 )

     

    Then create a measure as below:-

    Measure = 
CALCULATE(DISTINCTCOUNT('Table'[Sub-Product]),FILTER('Table','Table'[_no_stocks_left] = 1))

     

    Refer a file below:-

     

AV_04's avatar
AV_04
Helper I
3 years ago

Hi Samarth, 

 

Thanks for your response, Apologies if my question wasn't clear. My requirement is just to count the sub category. For example, in the sub category field of lewis, the count of 1 in bought by customer field is 4 which is equal to the value in stocks available(4), only when they are equal I need to consider and count those instances. Could you kindly let me know if my question wasn't clear, I can try to rephrase it again. Thanks for your help.

Samarth_18's avatar
Samarth_18
Community Champion
3 years ago

Above code satify this condition right?
Could you please share your final required output based on provided data?