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

1. ## 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. ## 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.

3. ## 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.

4. ## 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

5. ## 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. ## 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. ## 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. ## 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

9. ## 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. ## Re: Trying to calculate average with a Pivot table and/or Power query.

Refresh thread and see attached excel file

11. ## 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.

12. ## 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

13. ## 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. ## 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
• 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
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Close this window,

After that you should see attachment in your post

15. ## 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. ## 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

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

#### 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