Forum Discussion

Nayababbasi91's avatar
Nayababbasi91
Frequent Visitor
1 year ago
Solved

How to calculate sub-product quantity

Hi 

 

I would like to know how to calculate the quantity of sub-products using the table provided.

 

Once I have the sub-product total Qty, I can create a measure to calculate % by Sub-product. 

 

Please advise. 

Need Help
  • DataNinja777's avatar
    DataNinja777
    1 year ago

    Hi Nayababbasi91 ,

     

    You can produce your required output by writing dax measures like below:

    Sub-product (all) = calculate(sum('Table'[Brand Qty]),all('Table'[Sub-product]))

    Then use the measure above in another measure to calculate the % share of the total.

    Sub-product quantity% = VAR Quantity= sum('Table'[Brand Qty])
return 
divide (Quantity,[Sub-product (all)])

    The resulting output looks like below:

     

    Best regards,

     

4 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    1 year ago

    Hi Nayababbasi91 ,

     

    You can produce your required output by writing dax measures like below:

    Sub-product (all) = calculate(sum('Table'[Brand Qty]),all('Table'[Sub-product]))

    Then use the measure above in another measure to calculate the % share of the total.

    Sub-product quantity% = VAR Quantity= sum('Table'[Brand Qty])
return 
divide (Quantity,[Sub-product (all)])

    The resulting output looks like below:

     

    Best regards,

     

  • Nayababbasi91's avatar
    Nayababbasi91
    Frequent Visitor
    1 year ago

    Thank you so much. 
    The only thing which isn't working is the variable:

     

     

    Quantity= sum('Table'[Brand Qty])

     

    I need to divide the total, which is 70, by each sub-product. 

     

    The sum gives the row total, not the grand total. 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from DataNinja777 , please allow me to provide another insight: 
    Hi  Nayababbasi91 ,

     

    Here are the steps you can follow：

    1. Create measure.

    Sub-product Qty =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Product]=MAX('Table'[Product])&&'Table'[Sub-product]=MAX('Table'[Sub-product])),[Brand QTY])
    % by Sub-product =
var _sumall=SUMX(ALL('Table'),[Brand QTY])
return
DIVIDE(
    [Sub-product Qty],_sumall)

    2. Result:

     

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    Write these measures

    BQ = sum(Data[Brand Qty])

    TBQ = calculate([BQ],all(Data[Product]),all(Data[sub-product]))

    Ratio = divide([BQ],[TBQ])

    Hope this helps.