Excel Power Pivot & Power Query For Dummies by Michael Alexander

Author:Michael Alexander [Alexander, Michael]
Language: eng
Format: epub
ISBN: 9781119844501
Publisher: Wiley
Published: 2022-01-28T00:00:00+00:00

FIGURE 7-9: The math falls apart when you move to any granularity above OrderNumber (such as Region).

In other words, the measure needs to retain what is known as row context. When a measure retains row context, it sees and can interact with values for each row in which it performs its intended operation. Unfortunately, the SUM function has no row context, no insight into individual row data. SUM aggregates all the data in the specified column without capturing or noticing individual row data.

This is where you turn to iterator functions. Iterator functions, sometimes referred to as X-functions, are designed to iterate through and perform operations on each individual row record before aggregating results. The Fab Four aggregate functions have iterator versions of themselves: SUMX, AVERAGEX, MINX, and MAXX.

Iterator functions require two arguments: the table to iterate through and an expression to apply to each row. As an example, you can use the following formula (see Figure 7-10):

=SUMX(Sales, Sales[OrderQuantity] * Sales[UnitPrice])

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.