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:-

     

Samarth_18's avatar
Samarth_18
Community Champion
3 years ago

Hi AV_04 ,

 

If I undestand your question correctly then below would be your code:-

_stocks =
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, stock_available + 1, stock_available )

 

Output:-

  • 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?

      • AV_04's avatar
        AV_04
        Helper I
        3 years ago

        Thanks samarth. I am halfway through the solution, I just need the distinct count of sub category that goes out of stock. Here in the above table the count will be only one (Lewis), incase if any other sub category within product satisfies the condition, we need to do count+1. I am not sure how to draw the resultant up in a table.

         

        Product                      Sub-Product         Bought by customer?           Stocks Available
        Sportslewis14
        Sportslewis14
        Sportslewis14
        Sportslewis14
        SportsJ112
        SportsJ112
        accessoriesJacks13
        accessoriesJacks13
        Sportschrome12
        Sportschrome12

         

        For the above table the resultant count should be 3 since lewis, j1 and chrome satisfies the condition. 

        I hugely thank you for the solution. I just started with my advanced dax journey hopefully can catch up soon.