# add a % on subtotal using DAX

• ### Question

• Hello,

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

 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

• 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 Tuesday, November 28, 2017 9:37 AM
Tuesday, November 28, 2017 1:14 AM

### All replies

• I am a beginner myself.

[Sales %] :=

DIVIDE([Product_group_sales], [sales])

• Proposed as answer by 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,

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 Monday, November 27, 2017 9:42 AM
• Unmarked as answer by Monday, November 27, 2017 9:42 AM
• Marked as answer by Monday, November 27, 2017 9:42 AM
• Unmarked as answer by 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,

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