Contents
main idea
Suppose that you and I are sitting in the admissions and examination committee and evaluate applicants who want to enter our university. The grades in various subjects for our candidates are as follows:
There is only one free seat, for example, and our task is to choose a worthy one.
The first thing that usually comes to mind is to calculate the classic GPA using the standard Excel function AVERAGE (AVERAGE).
At first glance, it seems that Ivan is the best fit, because. He has the highest average score. But then we remember in time that our faculty is called “Programming”, and Ivan has good grades only in drawing, singing and other physical education, but not very good in mathematics and computer science. The question arises: how to assign different importance (value) to our objects in order to take it into account when calculating the average? This is where the weighted average comes in.
The weighted average is the average taking into account the different value (weight, importance) of each of the elements.
In business, the weighted average is often used in tasks such as:
- assessment of a portfolio of shares, when each of them has its own value / riskiness
- evaluating project progress when tasks do not have equal weight and importance
- assessment of personnel according to a set of skills (competencies) with different significance for the required position
- etc.
Calculation of the weighted average by formulas
Let’s add one more column to our table, where we indicate some dimensionless points of importance of each subject on a scale, for example, from 0 to 9 when entering our programming faculty. Then we calculate the weighted average score for each applicant, i.e. average, taking into account the weight of each item. The formula we need will look like this:
Function SUMPRODUCT (SUMPRODUCT) multiplies each other in pairs in the cells in the two specified ranges – the grades of the applicant and the weight of each subject – and then sums up all the resulting products. Then the resulting sum is divided by the sum of all importance points to average the result. That’s all wisdom.
So we take Masha, and let Ivan enter the Institute of Physical Education 😉
Calculation of the weighted average in the pivot table
Raise the stakes and make it harder. Let’s say that now we need to calculate the weighted average, but not in the usual, but in the pivot table. Suppose we have the following table with sales data:
Please note that I converted it to a smart table with the command Home – Format as a table (Home — Format as Table) and gave her a tab Constructor (Design) name Data.
Please note that prices for the same item may vary. Our task: to calculate the weighted average prices for each product. Following the same logic as in the previous paragraph, for example, for strawberries that were sold 3 times, this should be:
=(691*10 + 632*12 + 957*26)/(10+12+26) = 820,33
That is, we sum up the costs of all transactions (the price of each transaction is multiplied by the quantity per transaction) and then divide the resulting number by the total quantity of this product.
True, with the implementation of this simple logic, it is in the pivot table that a small bummer awaits us. If you have worked with pivots before, then you probably remember that you can easily switch the value field of the pivot to the function we need by right-clicking on it and selecting the command Totals for (Summarize Values By):
This list has an average but no weighted average 🙁
You can partially solve the problem if you add an auxiliary column to the original table, where the cost of each transaction will be calculated:
Now we can throw cost and quantity into the range of values uXNUMXbuXNUMXbside by side – and we get almost what is required:
It remains to divide one by the other, but doing this, it seems, is a simple mathematical operation inside the summary is not so simple. You will either have to add a calculated field to the summary (tab Analysis – Fields, Items, Sets – Calculated Field), or consider it as a regular formula in adjacent cells, or use a function GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA)about which I have already written. And if tomorrow the dimensions of the summary (product range) change, then all these formulas will have to be manually adjusted.
In general, somehow everything is inconvenient, time-consuming and depressing. Moreover, an additional column in the source data needs to be done by hand. But there is a nice solution.
Calculating a weighted average in a PivotTable using Power Pivot and the DAX language
If you have Excel 2013-2016, then a super-powerful data analysis tool is built into it – the Power Pivot add-in, in comparison with which pivot tables with their capabilities are like abacus against a calculator. If you have Excel 2010, then this add-in can be downloaded completely free of charge from the Microsoft website and also installed for yourself. With the help of Power Pivot, the calculation of the weighted average (and other things that are impossible in ordinary summary pieces) is greatly simplified.
1. First, let’s load our table into Power Pivot. This can be done on the tab. PowerPivot button Add to Data Model (Add to Data Model). The Power Pivot window will open and our table will appear in it.
2. Then click in the formula bar and enter the formula for calculating the weighted average there:
A few notes on the formula:
- Power Pivot has its own built-in language with a set of features, tools, and a specific syntax called DAX. So we can say that this formula is in the DAX language.
- Here WA is the name of the calculated field (in Power Pivot they are also called arrangements), which you come up with yourself (I called WA, meaning Weighted Average – “weighted average” in English).
- Please note that after WA it is not equal, as in regular Excel, but a colon and equal.
- When you enter the formula, hints will appear – use them.
- After completing the formula input, press Enter, just like in regular Excel.
3. Now we are building a summary. To do this, in the Power Pivot window, select on the tab Home – Pivot Table. You will automatically return to the Excel window and see the familiar interface for building a pivot table and a list of fields in the panel on the right. It remains to throw the field Name into the area of rows, and our measure WA created by the formula into the area of values - and the problem is solved:
That’s it – beautiful and elegant.
General moral: if you work a lot and often with pivot tables and their capabilities are “cramped” for you – dig towards Power Pivot and DAX – and you will be happy!
- What the SUMPRODUCT function actually does
- Why do we need the function GET.DATA.PIVOTAL.TABLE
- Set up calculations in PivotTables