INTHEBLACK April 2022 - Magazine - Page 73
03
CLICK HERE TO ACCESS
Extra material, a podcast
and a video from
Neale Blackwood CPA
defined in the Measure dialog. You can click
the Check formula button (just above the
formula area) to see if you have any errors in
the formula. This is more helpful for complex
DAX formulas.
We can now remove the Quantity field from
the PivotTable. When referring to a table column
in a DAX formula, always include the table
name. This is best practice for DAX formulas.
04
SUMX DAX FUNCTION
The SUMX function is an iterator function.
Iterator functions have the X on the end of
an existing function name. This means it can
perform calculations based on records (rows)
within a table. In our case, we need to work
out the total sales for our products. We have
the quantity sold in the Sales table and the
unit price, but the total sales amount is not
included.
The SUMX function can multiply the
quantity by the unit price on a row-by-row
basis to calculate the total sales value. It then
adds up all multiplication results for each row
to return a single total value. Use the New
Measure option. The settings to use for the
Total Sales Measure are shown in Figure 04.
05
can create a Pivot Table using fields from all
the tables.
In the Power Pivot window on the Home
ribbon, click the PivotTable icon. Select New
Worksheet and click OK. Four tables will be
listed in the PivotTable Fields list. Drag the
fields as per Figure 02.
The columns are based on the Customer
Category field from the Customer table. The
rows are based on fields from the Date and
Product tables. The Quantity is a field in the
Sales table.
This is a typical report you create when you
start using Power Pivot. In a normal PivotTable,
you drag fields into the Values section. When
using the Data Model, it is best practice to use
Measures, not fields, in the Values section.
MEASURES
A Measure is a named formula that calculates
based on its location within the PivotTable. We
need to create a Measure for Total Quantity
Sold, which adds up the Quantity field in the
Sales table.
In Excel, you work with cells and ranges. In
DAX, you work with Tables and Table Fields
(columns). In the Excel window in the Power
Pivot tab, click the Measures drop-down and
choose New Measure. Figure 03 shows the
entries needed to create a Measure called
Total Quantity Sold. When you click OK, the new
Measure will appear in the PivotTable. Figure 03
also shows part of the updated report.
Note that the Measure in the report in
Figure 03 is formatted based on the format
DIVIDE DAX FUNCTION
Now that we have a measure for Total
Quantity Sold and Total Sales, we can work
the average selling price.
DAX has a function dedicated to dividing,
and it avoids the divide by zero error. We
will use it to calculate the Average Sell Price.
Figure 05 shows the settings for the new
measure and part of the resulting Pivot
Table report. Best practice when referring to
measures is to never include the table name.
This means that in a DAX formula anything
with just brackets around it is a measure.
Anything with a table name followed by a
name within brackets is a field in a table.
This makes editing and understanding DAX
formulas much easier.
M E AS U R E F O R M U L A T I P
In the Measure dialog and the Formula
section, you can hold the Ctrl key down
and use the mouse wheel to make the
formula larger.
intheblack.cpaaustralia.com.au April 2022 73