locked
add a % on subtotal using DAX RRS feed

  • Question

  • Hello,

    I'm trying create a new measure to calculate sales % below.  Could you suggest what DAX should I use.

    Appreciate for your help.

    Year Product Product_group sales Product_group_ sales sales %
    2016 A y 10 10 100%
    2016 B x 15 35 43%
    2016 C x 20 35 57%
    2017 A y 25 25 100%
    2017 B x 30 65 46%
    2017 C x 35 65 54%

    Cheers,

    Billy

    Friday, November 24, 2017 7:19 AM

Answers

  • Hi Billy,

    Thanks for your  response.

    If you want to create measure instead of calculated column, you may try below DAX formula:

    Total_SBU_qty =
    CALCULATE (
        SUM ( _SINGLE_SOURCE[Standard Quantity K] ),
        ALLEXCEPT (
            _SINGLE_SOURCE,
            _SINGLE_SOURCE[SBU],
            _SINGLE_SOURCE[Fiscal year],
            _SINGLE_SOURCE[Month_]
        )
    )
    Total_qty =
    SUM ( _SINGLE_SOURCE[Standard Quantity K] )
    
    Total_SBU_qty_% =
    DIVIDE ( [Total_qty], [Total_SBU_qty] )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Billy_MSDN Tuesday, November 28, 2017 9:37 AM
    Tuesday, November 28, 2017 1:14 AM

All replies

  • I am a beginner myself.

    But how about it below?

    [Sales %] :=

    DIVIDE([Product_group_sales], [sales])

    • Proposed as answer by dluhut Friday, November 24, 2017 6:03 PM
    Friday, November 24, 2017 6:01 PM
  • Dear all

    I missed to mention that only the first 4 columns of database are there.  Indeed, I've problem with creating Product_group_ sales using DAX and my ultimate target is to have sales %.

    Any suggestion is welcome.

    Dear Dluhut, thanks for your hint.

    Cheers,

    Billy

    Monday, November 27, 2017 1:19 AM
  • Hi Billy,

    Thanks for your question.

    In this scenario, you can create a calculated column called Product_group_ sales as below:

    =
    CALCULATE (
        SUM ( Sales[Sales] ),
        ALLEXCEPT ( Sales, Sales[Product_group], Sales[Year] )
    )

    Then you can create another calculated column called sales % as below:
    =
    DIVIDE ( Sales[Sales], Sales[Product_group_sales] )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Billy_MSDN Monday, November 27, 2017 9:42 AM
    • Unmarked as answer by Billy_MSDN Monday, November 27, 2017 9:42 AM
    • Marked as answer by Billy_MSDN Monday, November 27, 2017 9:42 AM
    • Unmarked as answer by Billy_MSDN Tuesday, November 28, 2017 9:37 AM
    Monday, November 27, 2017 1:47 AM
  • Hi Wilson,

    Instead of creating a new column, I've created a new measure because I want the result would be determined depending level of details I

    selected.  Product_group_ sales (it called _SINGLE_SOURCE[total_SBU_qty] in my model) works well, however, I got an error message when I was trying to add the new measure %_total_SBU_qty using

    =divide(_SINGLE_SOURCE[Standard Quantity K],_SINGLE_SOURCE[total_SBU_qty])

    Could you advise what I went wrong.  Thank you.

    Monday, November 27, 2017 6:34 AM
  • Hi Billy,

    Thanks for your quick reponse.

    would you mind sharing all the DAX formula that you are using and the detailed error messages you have encountered?

    One more thing, please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Monday, November 27, 2017 7:02 AM
  • Hi Wilson,

    Please see my formulas & error message below, thanks a lot.

    total_SBU_qty

    = calculate(

       

    sum(_SINGLE_SOURCE[Standard Quantity K]),

       

    ALLEXCEPT(_SINGLE_SOURCE,_SINGLE_SOURCE[SBU],_SINGLE_SOURCE[Fiscal year],_SINGLE_SOURCE[Month_]))

    %_total_SBU_qty

    = divide

    (_SINGLE_SOURCE[Standard Quantity K],_SINGLE_SOURCE[total_SBU_qty])

    Monday, November 27, 2017 10:27 AM
  • Monday, November 27, 2017 10:29 AM
  • Hi Billy,

    Thanks for your  response.

    If you want to create measure instead of calculated column, you may try below DAX formula:

    Total_SBU_qty =
    CALCULATE (
        SUM ( _SINGLE_SOURCE[Standard Quantity K] ),
        ALLEXCEPT (
            _SINGLE_SOURCE,
            _SINGLE_SOURCE[SBU],
            _SINGLE_SOURCE[Fiscal year],
            _SINGLE_SOURCE[Month_]
        )
    )
    Total_qty =
    SUM ( _SINGLE_SOURCE[Standard Quantity K] )
    
    Total_SBU_qty_% =
    DIVIDE ( [Total_qty], [Total_SBU_qty] )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Billy_MSDN Tuesday, November 28, 2017 9:37 AM
    Tuesday, November 28, 2017 1:14 AM
  • Hi Wilson,

    Thanks a lot.

    I list my power pivot below and manual added the variances in blue.

    Could you share with me how I could create measures to calculate year over year 1) volume variance, 2)mix variance & 3) others highlighted in yellow.  Please see the formulas on the right.  My challenge is that the quantity, sales and margin are not listed by years in my source data.

    Could you also suggest to how to use something similar as "iferror" in DAX which returns result in 0 when dividing by zero.  I got his problem in calculating average price where the volume is missing.

    Cheers,

    Billy

     

    Tuesday, November 28, 2017 10:44 AM