Microsoft Office Tutorials and References
In Depth Information
In case you want to make a monthly average of the total sales, you should use the
number of months in the denominator of the ratio, as in the following expression that
you can use to define the MonthlyAverage measure:
MonthlyAverage = IF( COUNTROWS( VALUES( OrderDate[Month] ) ) > 0,
SUM( SalesOrderDetail[LineTotal] )
/ COUNTROWS( VALUES( OrderDate[Month] ) ),
BLANK() )
Please note that this definition does not work with a selection of more than one year.
To avoid this issue, you need to create a calculated column in the OrderDate table
with a concatenation of year and month so that the distinct number of values over
a period take account also of the year and not just the month. If you do that, you
must replace that column to the Month column used in the formula above.
To make a monthly average of the year-to-date sales, you have to replace the
corresponding YtdLineTotal measure at the numerator and you need a CALCULATE
expression at the denominator so that you can calculate the number of months included in
the year-to-date calculation:
YtdMonthlyAverage = IF( COUNTROWS( VALUES( OrderDate[Month] ) ) > 0,
SalesOrderDetail[YtdLineTotal]
/ CALCULATE( COUNTROWS( VALUES( OrderDate[Month] ) ),
DATESYTD( OrderDate[Date] ) ),
BLANK() )
As you can see, the expression might be different according to the calculation you have
to do. You have to pay particular attention to the calculation necessary for numerators
and denominators of any ratio and average measure.
Difference over Previous Year
A common operation that compares a measure with its value in the prior year is to calculate
the difference of these values. That difference might be expressed as an absolute value or
by using a percentage, as you can see in the CH07-09-Yoy.xlsx workbook included on the
companion DVD. To make these calculations, you need the value for the prior year that you