locked
% of yearly total RRS feed

  • 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,
    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 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.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 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,
    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 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