If your query has more than one dimension and are trying to simplify the results in a pivot table, you will want to use weighted averages in order to ensure the most accurate data.
For example, take this query:
When it was exported in Excel, it has all of the Management Zone unique values, including High and Low Checks.
If we wanted to combine that data in the appropriate zones and put it into a Pivot Table, it would be a straight average, rather than a weighted average. To make sure that your Pivot Tables have weighted averages, watch this video!
Or follow these steps:
- Do any editing of attribute names that you need to (For this example, a "Find and Replace" was done to remove the "High Check" and "Low Check" from the names.)
- Create a new column of data, giving it a unique name that you will be able to locate from the list of header names later. (For this example, it has been called "Ttl Yld".)
- Calculate the Average column of the attribute that you are wanting to combine in your Pivot Table. (For this example, average yield is the attribute.)
- Fill the cells either by pulling down on the lower right-hand side of the selected cell.
- Or left click on the cell and while continuing to hold down, pull down and highlight all of the cells in the column until you reach the end of your rows of data. Then, click "Ctrl" + "D" and it will fill (Fill Down) the cells with the same formula.
- Now that you have the column populated with calculated values.
- Insert a Pivot Table.
- Make any changes if you need to of the data range or placement.
- After you have added the desired rows, navigate to the "PivotTable Analyze" tab, select the "Fields, Items, & Sets" dropdown and choose "Calculated Field...".
- Change the Field Name, then in the "Formula" area, add the Unique Field that you created on the Summary page, then click the "Insert Field" button.
- Type in a "/", locate the "Acres" field and click the "Insert Field" button again.
- Then click the "OK" button.
- Now your averages are weighted!
- As you use slicers, your averages will continue to be weighted!
