Microsoft Office Tutorials and References
In Depth Information
Common Calculations for ABC Analysis
If you consider the three previous examples of ABC classification from a DAX point of
view, you can see that they look very similar. However, there are important differences,
depending on the data model, and each scenario has some different details in the DAX
expressions you use. For this reason, you have to pay attention to the data model first,
and then, to retrieve the correct values, you need to use your own tables and columns to
replace the correspondent names in the formula we showed you in the previous example.
Event in Progress
It might be useful to calculate the number of events that are active at a certain date. This value
can be used, for example, to plot a chart of the orders in place over time. To make this
calculation, you need to import just the SalesOrderHeader table from the AdventureWorks database,
and create a Calendar table in Excel as you saw in Chapter 7, “Date Calculations in DAX.” That
table has to be imported as a linked table in PowerPivot. You can find the complete model for
this example in the CH09-09-EventInProgress.xlsx workbook included on the companion DVD.
Note The scenario described in this section was originally designed by Chris Webb and
described at http://tinyurl.com/DaxEventInProgress , which is a post of his blog available at
http://cwebbbi.spaces.live.com/Blog .
To get the number of orders in place at a certain date, you need to count the number of rows
in the SalesOrderHeaders table that have an OrderDate less than or equal to the given date
and that have a ShipDate greater than or equal to that same given date. You will see how to
make this calculation as both a measure (which is calculated dynamically using active filters in
the PivotTable) and a calculated column.
The first calculation is a measure that you can use in the PivotTable browsing Calendar data. Before
creating it, you need to make sure that there are no relationships between SalesOrderHeader and
Calendar tables! This is important because you are going to use a DAX calculation that would not
work if a relationship existed between these two tables. You can define the OrdersInPlace measure
by using this DAX expression:
OrdersInPlace = CALCULATE( COUNTROWS( SalesOrderHeader ),