Aggregating data

Although the type of aggregation of a measure can be changed from the Marks pane, it is sometimes necessary to show different aggregations of the same measure, and we can do this by creating multiple aggregate fields. We can also add the same Measure field multiple times to the Rows or Columns shelf and then change the aggregation type.

Getting ready

Let's use the sample file Sample – Coffee Chain (Access). Open a new worksheet and select Sample – Coffee Chain (Access) as the data source.

How to do it...

Once the data is loaded on the worksheet, perform the following steps to create calculated fields with different aggregations:

  1. Right-click on Profit from Measures, and click on Create Calculated Field.
  2. In the Name box, enter Sum of Profit.
  3. In the Formula box, enter the SUM([Profit]) formula and hit OK.
  4. Right-click on Profit from Measures and click on Create Calculated Field.
  5. In the Name box, enter Average of Profit.
  6. In the Formula box, enter the AVG([Profit]) formula and hit OK.
  7. Right-click on Number of Records from Measures, and click on Create Calculated Field.
  8. In the Name box, enter Count Number of Records.
  9. In the Formula box, enter the COUNT([Number of Records]) formula and hit OK.
  10. Click on the Show Me button to display the Show Me toolbar on the screen.
  11. Select Sum of Profit, Average of Profit, and Count Number of Records from Measures and Type from Dimensions.
  12. Click on text tables on the Show Me toolbar to create a table as shown in the following screenshot:
    How to do it...
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset