Creating a measure using the count function

The aggregation of a measure is determined by the Aggregate Function property. There are five possible aggregations: Sum, Min, Max, Count and Distinct Count. Any other aggregated value also can be created by using calculated measures. We will examine here how the count function operates. The Sum, Min and Max functions operate only on numeric columns. The Count function operates on non numeric columns also. It does not count the unique occurrences of a key. For this the Distinct Count function is used.

One of the most important uses of the Count function is to allow the creation of a calculated measure that properly calculates an average. The calculated measure is created with the Count function as the basis.

Click the Insert Measure toolbar button, and double click the Employee_Id and type Count for the Name property of the new measure, and change the value of the Aggregate function property to count.

Click the Process cube button and save the cube. Since Count is a regular measure and not a calculated measure it is important to process the cube after adding it.

Say no to designing aggregations and accept the proposed processing method. Close the process log window and scroll the data pane to see the values in the cube.

Click the Insert Calculated Member toolbar button. In the Calculated Member builder dialog box type Average Units as the Member name.

Expand the Measures dimension and Double click the sales units measure, click the (/) and double click the Count measure. Then click OK.

Select the Average Units measure in the Calculated members folder, and for its format setting string property, select #,# from the list.  Press Enter and scroll to see the values in the Data Pane.

The count function allows the user aggregate the row count from the fact table in parallel with the value that has to be averaged.  The calculated member can then be created with the proper weighted average at each level of the hierarchy.

Hiding Internal Measures

A user may want to use a measure an intermediate value for creating a calculated measure, but may need to hide it from client applications. This property of the measure can be hidden by setting the visible property of the measure to false. It disappears from the list of measures and even from the Calculated Member Builder dialog box. Though this measure can still be used in expressions, it will have to be manually typed in.

First Page: Tutorial 40: MSAS: Introduction and Working with Measures