LECTURE 04/03/2024
Different versions of the measure Transactions in PowerPivot:
- Transaction: this measure is simply taking any filter that we’re creating with the Pivot
- Transaction to Bachelors: still passive against the filters (colors and english
education), but given the rows the table is exposing due to the filters, in this measure
we’re taking a subset of them. We’re selecting between the visible rows, only the
rows with a specific feature
- ALL Transactions: complete indifference towards the filters, it counts all the rows of
the table
- ALL Transactions Bachelors: still ignore the filters, but inject the selection with a filter
function (EnglishEducation=Bachelors).
Variations of Sales measure:
- Sales: =SUM(Sales[Sales Amount])
- Sales Bachelors: =
→ we can’t use the SUM function, since it only accepts a column name. That’s an
issue, since I need to use a FILTER function and we can’t do a SUM on a FILTER.
The SUM has a maximum argument count of one/it only accepts one input.
When we use the SUM function, the software in reality internally calls another
function, which is called SUMX → This function asks first for a table, to go row by
row, and row by row it can compute an expression.
ex. Pretend we don’t have the SalesAmount column, but only a column with UnitPrice
and a column with OrderQuantity.
We should calculate the product between UnitPrice and OrderQuantity to find the
revenue row by row and then sum the results at the end. This is what SUMX does.
Iterators: functions that go by row in a table, calculate something in every row, keep
in memory the results and then sum/make the average/…
SUM → SUMX
AVERAGE → AVERAGEX
MIN → MINX
MAX → MAXX
Variations of Sales measure without the SalesAmount column:
-Sales: = SUMX(Sales,Sales[UnitPrice]*Sales[OrderQuantity])
→ SUMX creates a temporary column in memory in which calculates in every row the
amount of sales, aggregates that with a sum, gives me the result and then the column is
