Describe and implement aggregate functions
Power BI uses the term aggregate to refer to mathematical functions that it executes on values obtained from data sources. When Power BI evaluates data, it automatically aggregates certain data types to anticipate the needs of the developer in creating a report. For example, when Power BI imports a table containing sales figures for a company’s branch offices, it might aggregate those figures using the sum function to add up totals for the product sales categories. Although Power BI does this automatically, it is still possible for developers to modify the automatic aggregations or apply new aggregations to data as needed.
It is the Value element in a visualization that is typically aggregated by Power BI during its evaluation of the data. For example, in Figure 3-49, the values represented in the column chart are taken from the data source’s Units Sold field. Hovering the mouse cursor over that field in the Value well indicates that the amounts used to create the chart are sums of the units sold. In the original source data, each product has dozens of Units Sold values for each product, broken down by country. Power BI has added the values for each product to arrive at the totals used for the chart.
FIGURE 3-49 Power BI visualization with indication of aggregate
If for any reason a developer does not want to use sums of the units sold to create the visualization, it is possible to change the aggregate by right-clicking the field in the Value well to display the list of functions shown in Figure 3-50. For example, selecting Average instead of Sum causes Power BI to recalculate the values and redraw the chart with the averages instead of the sums. Other aggregates that Power BI supports include minimum, maximum, count (distinct), count, standard deviation, variance, and median.
FIGURE 3-50 Power BI visualization with aggregate context menu
It is also possible to aggregate a text field, but Power BI obviously does not support mathematical functions such as sum and average for this purpose. Only functions such as count, distinct count, first, and last are applicable.