Summing aggregates and row iteration
In the recipes so far, we have looked at creating measures based on a simple summation. With these types of calculations, we simply define a measure as the sum of the respective columns. The calculations can then be used by other calculations and the result would be the same as if the calculation had been done within the row and aggregated. For example, if we define a measure of [Sales]
and a measure of [COGS]
(cost of sales), we could define a new measure as [Profit]:=[Sales]-[COGS]
. Alternatively, we could add a column to the Sales
table as line_profit
with a column formula (SALES-COGS)
and sum that column to create the new calculation.
However, in some situations, creating measures based on other measures does not provide the correct result, and it is necessary to determine the result of the calculation for each row and then perform a function on the results. DAX includes some handy functions for achieving this outcome without the need for intermediary...