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

already defined in PyLineTotal:

PyLineTotal = CALCULATE( SUM( SalesOrderDetail[LineTotal] ),

SAMEPERIODLASTYEAR( OrderDate[Date] ) )

The absolute difference of LineTotal over previous year (year-over-year, YOY) is a simple

subtraction. You can define a YoyLineTotal measure with the following expression:

YoyLineTotal = SUM( SalesOrderDetail[LineTotal] ) - SalesOrderDetail[PyLineTotal]