locked
Sum Totals/Average in Power Pivot RRS feed

  • Question

  • Hello,

    I have created some calculated tables in powerpivot based on sales data. I have arranged my products vendors etc into a heirarchy and the pivot sums to sales volumes accordingly for each week. My problem is that I need to total column at the end to show an average sales value for each week rather than summing it (currently it adds the values for week 1,2,3 and 4 etc). Can I do anything about this in powerpivot - when I look in field settings the option for average is greyed out?

    Thanks if anyone can support with this.

    Monday, January 5, 2015 6:42 PM

Answers

  • Use this measure:

    AverageXSumTotalSales:=
    AVERAGEX(
        VALUES( Forecast[Week Number] )
        , SUM( Forecast[Total Sales Value] )
    )

    AVERAGEX() iterates over the rows of the table passed to it and evaluates the expression passed as its second argument for each row in that table, adding each value to a running total, and then divides by the number of rows in the table.

    The table we pass is VALUES( Forecast[Week Number] ). VALUES() returns a list of all the unique values which make up the column passed to it, thus in a detail column in the pivot table it is a 1x1 table. Any averaging we do here is the same as just evaluating the second expression in AVERAGEX() (a simple sum).

    At the total level, it calculates the average of the values of the SUM() for each week.

    • Marked as answer by Grinch356 Tuesday, January 6, 2015 12:15 AM
    Monday, January 5, 2015 8:47 PM

All replies

  • Grinch,

    You need to add a calculated field (also called Measure) which used AVERAGE instead of SUM.

    Average Sales:=AVERAGE(Sales[SalesAmount])

    If you send us more information (see link below about asking Power Pivot questions), we may be able to provide more specific help.


    Regards, Avi www.powerpivotpro.com PowerPivotPro

    Wiki:How to ask a Power Pivot Question to get a prompt, accurate and helpful response

    Monday, January 5, 2015 6:46 PM
  • Mt apologies - I have attached a file with a text box explaining to make it clearer. It is just the grand total column at the end that needs to use average.

    Thanks for your quick response.

    Monday, January 5, 2015 8:35 PM
  • Use this measure:

    AverageXSumTotalSales:=
    AVERAGEX(
        VALUES( Forecast[Week Number] )
        , SUM( Forecast[Total Sales Value] )
    )

    AVERAGEX() iterates over the rows of the table passed to it and evaluates the expression passed as its second argument for each row in that table, adding each value to a running total, and then divides by the number of rows in the table.

    The table we pass is VALUES( Forecast[Week Number] ). VALUES() returns a list of all the unique values which make up the column passed to it, thus in a detail column in the pivot table it is a 1x1 table. Any averaging we do here is the same as just evaluating the second expression in AVERAGEX() (a simple sum).

    At the total level, it calculates the average of the values of the SUM() for each week.

    • Marked as answer by Grinch356 Tuesday, January 6, 2015 12:15 AM
    Monday, January 5, 2015 8:47 PM
  • Thanks very much,

    I can use that but is there any way I can just see it once. When I add it in I get an average after each week, I just want to see the current running average ideally so only want to see it once. I can live without though.

    Also, I wish to add a KPI to this measure I have different targets depending on the family. Example below, I know if I had an absolute target that I could add this in but can it be done if different categories have different targets?

    Family Target for Average Sales Value]
    ACCESSORY 2052120
    BROADBAND 16000
    CARDS 117720
    DISPLAY UNIT 2640
    GIFT WITH PURCHASE 315200
    HANDSET 4791560
    LICENCES 12760
    MOBILE BROADBAND 71280
    MSACCESSORY 4760
    MSHANDSET 8680
    MSTABLET 6280
    SALETABLET -2000
    SIM 15591400
    TABLET 207880
    WEARABLE 19160

    Monday, January 5, 2015 9:19 PM
  • Hi,

     

    I have figured this out now - didnt want anyone spending their time on it now I have it.

    Thanks for the above answers - really helpful as always.

    Tuesday, January 6, 2015 12:15 AM
  • Hi Grinch,

    It seems that your issue had been solved by yourself. Could you share the solution so that it will help other forum members who have the similar issue.

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, January 6, 2015 8:09 AM