# % of yearly total

• ### Question

• Hi,I need to calculate % of sales by period.

I have salesfigures by month and the total for the year

Jan    Feb    Mar  ...  Total

30      15     15         300

10%    5%   5%          100%

How do I calculate this

Lars Wärvik

Wednesday, November 23, 2016 12:43 PM

• Hi larraw,

First, please unpivot the table in Power Query. Your sample data structure is like first picture. In the Power Query Edit window, select all the column and click unpivot highlighted in yellow-> Close&Load, you will get the result table like picture three below.

Then add the data into model, create a measure using the following formula.

`percentage1:=CALCULATE(SUM(Table1_2[Value]),ALLEXCEPT(Table1_2,Table1_2[Attribute]))/CALCULATE(SUM(Table1_2[Value]),ALL(Table1_2))`

Create a pivot table, select the Attribute field as Row level, the Value field, the percentage1 measure as Value level, you will get the expected result.

Best Regards,
Angelia

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.

• Proposed as answer by Friday, December 9, 2016 10:03 PM
• Marked as answer by Tuesday, December 20, 2016 11:16 AM
Thursday, November 24, 2016 2:00 AM

### All replies

• Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
Three ways, all with daily data for a year.
Aggregation of aggregated data may result in inaccuracies.
http://www.mediafire.com/file/792qj56q962m4vj/11_23_16.xlsx

Wednesday, November 23, 2016 11:21 PM
• Hello Lars

Combination of a few simple functions allows us to find ratios in DAX. We use DAX function DIVIDE(<numerator>, <denominator> [,<alternateresult>])

where we divide partial sum of the measure (numerator) by grand total of the same measure (denominator). CALCULATE function inside denominator creates a filter context.

Period_%: = DIVIDE (

SUM ( YourTableName[Sales] ),  ---  Numerator

CALCULATE ( SUM ( YourTableName[Sales] ), ALL ( YourTableName ) )  --- Denominator

)

*** this formula will work properly only if you do not use any slicers or filters.

• Edited by Thursday, November 24, 2016 1:34 AM
• Proposed as answer by Monday, December 5, 2016 6:47 AM
• Unproposed as answer by Wednesday, December 14, 2016 4:06 AM
Thursday, November 24, 2016 1:32 AM
• Hi larraw,

First, please unpivot the table in Power Query. Your sample data structure is like first picture. In the Power Query Edit window, select all the column and click unpivot highlighted in yellow-> Close&Load, you will get the result table like picture three below.

Then add the data into model, create a measure using the following formula.

`percentage1:=CALCULATE(SUM(Table1_2[Value]),ALLEXCEPT(Table1_2,Table1_2[Attribute]))/CALCULATE(SUM(Table1_2[Value]),ALL(Table1_2))`

Create a pivot table, select the Attribute field as Row level, the Value field, the percentage1 measure as Value level, you will get the expected result.

Best Regards,
Angelia

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.

• Proposed as answer by Friday, December 9, 2016 10:03 PM
• Marked as answer by Tuesday, December 20, 2016 11:16 AM
Thursday, November 24, 2016 2:00 AM