Answered by:
% 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
Answers

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,
AngeliaMSDN 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 Michael Amadi Friday, December 9, 2016 10:03 PM
 Marked as answer by AV111Editor 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.xlsxWednesday, 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 Nick Chobotar Thursday, November 24, 2016 1:34 AM
 Proposed as answer by Nick Chobotar Monday, December 5, 2016 6:47 AM
 Unproposed as answer by Nick Chobotar 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,
AngeliaMSDN 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 Michael Amadi Friday, December 9, 2016 10:03 PM
 Marked as answer by AV111Editor Tuesday, December 20, 2016 11:16 AM
Thursday, November 24, 2016 2:00 AM