+ Reply to Thread
Results 1 to 16 of 16

Trying to calculate average with a Pivot table and/or Power query.

  1. #1
    Forum Contributor
    Join Date
    08-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    128

    Trying to calculate average with a Pivot table and/or Power query.

    How can I get an average for a row in a pivot table and/or power query? I have many columns of data. 6 of these have percentages. So I thought I would be smart and make a custom column in power query to get the average of each row of these specific 6 columns and then hide the original columns making one master average column.

    I know I can make a custom column add columns by going:

    =[Name]+[Name2]

    But working out the average formula seems to baffle google. I know It would be easy to just throw this in a table and use the AVERAGE formula, but I want to use this data model for a specific purpose and that would negate that.

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Trying to calculate average with a Pivot table and/or Power query.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Forum Contributor
    Join Date
    08-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    128

    Re: Trying to calculate average with a Pivot table and/or Power query.

    Hopefully the attached will clear things up.

    First tab is how the data is send to us (annoyingly in PDF format first).

    Then there are 4 groups of data which we just want to blend/average as one.
    The second tab shows what I want a pivot table or in power query to do. I have no need for all the colour group columns, only the final averaged data.
    Doing this in excel is easy, but in a pivot table or power query, baffled.
    Attached Files Attached Files

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Trying to calculate average with a Pivot table and/or Power query.

    You can prepare source to be a table in PowerQuery (or manually if you want), means: single headers row and data in appropriate columns, the same type of data in the same column
    forget about colors in the result from PivotTable or from PowerQuery (you can do that manually after)
    No blank columns without header and all headers should be different because two WHT WHT will be changed to WHT WHT_1 etc or something like that
    No merged cells

    Both: PivotTable and PowerQuery works with columns and content


    sample of table
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    1
    Group Region Company H&S WHT RB DP DBS SG
    2
    Red Region 1 Aivee 42804 100 100 100 100 100
    3
    Red Region 1 Mycat 43108 100 100 100 100 100
    4
    Red Region 1 Vidoo 43025
    5
    Red Region 1 Divanoodle 42783 75 100 100 100 100
    6
    Red Region 1 Zoombox 42867 100 100 100 100 100
    7
    8
    Blue Region 1 Aivee 42804 100 100 100 100
    9
    Blue Region 1 Mycat 43108
    10
    Blue Region 1 Vidoo 43025
    11
    Blue Region 1 Divanoodle 42783
    12
    Blue Region 1 Zoombox 42867
    13
    Blue Region 1 Topiclounge 43111 50 100 100 100
    14
    15
    Green Region 1 Yamia 42984
    16
    Green Region 1 Skyndu 42618 100 100 100 100 100
    17
    Green Region 1 Yodo 43110
    18
    Green Region 1 Jaloo 42979
    19
    Green Region 1 Kanoodle 42614 100 100 100 100 100
    20
    21
    Purple Region 3 Twitterbridge 43049 67 67 100 100
    22
    Purple Region 3 Photobug 42460
    23
    Purple Region 3 Chatterpoint 42992 100 100 75 100
    24
    Purple Region 3 Yombu 43018 100 100 100 100
    25
    Purple Region 3 Livetube 42620 100 67 100 100


    blank rows are intentional
    Last edited by sandy666; 03-26-2018 at 06:39 PM.

  5. #5
    Forum Contributor
    Join Date
    08-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    128

    Re: Trying to calculate average with a Pivot table and/or Power query.

    I think I get you, in that case, once I have done all that, how do I get the averages on the rows using those specific columns?

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Trying to calculate average with a Pivot table and/or Power query.

    Maybe like this (PowerQuery)

    grp.jpg

    btw. table in post #4 is not averaged table

  7. #7
    Forum Contributor
    Join Date
    08-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    128

    Re: Trying to calculate average with a Pivot table and/or Power query.

    Thanks that looks great!
    I forgot to ask before (table didn't load), is there an easy way of getting that colour column to transpose like that?
    Or is it a case of manually having to make the first "group" column and assigning the values myself?

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Trying to calculate average with a Pivot table and/or Power query.

    PivotTable and PowerQuery is not a PowerPoint or Publisher. It works with data not with colours sorry

    that is why I defined groups (like you in the first row in your raw data)

    edit:
    ok, evening of goodness for people
    Attached Files Attached Files
    Last edited by sandy666; 03-26-2018 at 07:23 PM.

  9. #9
    Forum Contributor
    Join Date
    08-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    128

    Re: Trying to calculate average with a Pivot table and/or Power query.

    Sorry, I know. What I meant by colour is the first column in your table example, the group column.
    You have moved that from a row to a column. Was that an automated process or did you have to manually do that?
    I ask because the end game of this is to dump data in a sheet (with minor tweaks) and then magic happens. I don't want to have to jiggle all that data around for these weekly reports.

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Trying to calculate average with a Pivot table and/or Power query.

    Refresh thread and see attached excel file

  11. #11
    Forum Contributor
    Join Date
    08-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    128

    Re: Trying to calculate average with a Pivot table and/or Power query.

    Many thanks for your help and pointing me in the right direction.
    I think I found an alternative solution, however perhaps you can help me with a side issue related to the above?

    I have managed to blend some tables together and everything looks great. Yet oddly the grouping function seems to be giving me wrong information.
    If I export the data to a table and use a pivot table on the data (averaging the columns) it shows a correct value, in this example 85%
    However, if I use power query to group the rows (I'm assuming this is exactly how a pivot table would do it) the end average is way off, in this example it goes down to 35%.

    How would power queries averaging be do different to a pivot table?

    Edit: When grouping, it seems to count the null entries as a zero value where once exported the cell is empty, which is what I want.
    Last edited by X82; 03-28-2018 at 04:37 PM.

  12. #12
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Trying to calculate average with a Pivot table and/or Power query.

    PowerQuery : SUM of AVERAGES of groups then AVERAGE of all - (SUM A / COUNT A + SUM B / COUNT B) / COUNT ABC

    PivotTable : AVERAGE of SUMS - SUM / COUNT
    Attached Files Attached Files
    Last edited by sandy666; 03-28-2018 at 08:29 PM.

  13. #13
    Forum Contributor
    Join Date
    08-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    128

    Re: Trying to calculate average with a Pivot table and/or Power query.

    Thanks.
    Well that is, frustrating.

    Is there any way to rectify this? Ideally I want the grouping to average to be do as the pivot table does.
    Or is this a case of, it's the way it is and if I want it how I want it I would have to use a pivot table?
    I only fear on using a pivot table because I will be referring to cells in a pivot and I worry updating the table would break the link or link to the wrong cell.

  14. #14
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Trying to calculate average with a Pivot table and/or Power query.

    it depends on what you need. How should I know?
    maybe use DataModel and PowerPivot and calculate the average there

    best to attach the Excel file with before / after, means to show what you want to achieve. prepare it manually if necessary


    In attached Excel file try not to use:
    • merged cells
    • password protection
    • unnecessary formatting like: colours, borders, aligning another than default, etc...
    • unnecessary zooming/grouping/freezeing
    You have words to logically describe the problem

    To attach an Excel file to your post,
    • desensitize data
    • remeber that your example should reflect structure and type of data
    • click Go Advanced,
    • scroll down until you see Manage Attachments,
    • click that and select Browse,
    • select your file and click Open,
    • click Upload and you will see your attachment below Upload Files from a website
    • click Close this window,
    • click Submit reply

    After that you should see attachment in your post

  15. #15
    Forum Contributor
    Join Date
    08-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    128

    Re: Trying to calculate average with a Pivot table and/or Power query.

    What I need is for the query editor to stop averaging null values as a zero, which it seems to do after I have multiple groups.
    Looks like I will have to go the easy route and just export the table and use a pivot table on it. The data I have right now is too complex and time consuming to remove sensitive information and produce a dummy sample.

  16. #16
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Trying to calculate average with a Pivot table and/or Power query.

    you can do that easiest way,
    • insert pivot table
    • use an external data source
    • choose connection
    • select that query what you want
    • OK

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. error in power query & power pivot
    By Baldev Kumar in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 01-03-2018, 01:34 AM
  2. Replies: 3
    Last Post: 12-03-2017, 01:41 PM
  3. Calculate Average from Pivot Table
    By mazcarate in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-07-2017, 01:36 PM
  4. [SOLVED] Pivot table from power query
    By pccamara in forum Excel Charting & Pivots
    Replies: 12
    Last Post: 05-17-2017, 10:09 AM
  5. Pivot Table from MS Query to Calculate Orders
    By mars242 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-07-2013, 04:59 AM
  6. How can I calculate a weighted average in a Pivot Table?
    By petevang in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-23-2012, 12:56 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1