# Semi-additive measure to find an average based on various dimension attributes

### Question

• I need what i think is a type of semi-additive measure that works like below.

For the month of October:

The total line should sum rather than average (using the Avg function would return a total of 15 avg people).

The People column is what i'm having trouble with. My fact table has a column that is getting me the # of people by Month, Name, and I have it as a cube measure of Distinct Count. I'm unsure of how to write the mdx to get the behavior above.

• Edited by Wednesday, October 30, 2013 5:10 PM
Wednesday, October 30, 2013 5:07 PM

### All replies

• Confusing, can you elobarate a bit what you are getting now and what you want to achive and your column of interest

Regards

Nishar

Wednesday, October 30, 2013 5:31 PM
• I have a fact table that looks like this:

Count is a sum measure and People is a distinct count measure in the cube. I need to create a calculation that takes Count/People. However, I need it to work in this manner:

For the first record (A), the average should be 1/10. For the second (B), it should be 4/20. Since the calculation is currently Count/People, for the Total line (A and B combined) it will give me 5/15. However, I want the total row to calculate such that it uses the sum of people for A and B, giving me 5/30.

Wednesday, October 30, 2013 6:04 PM
• Great, You need to sum the count based on the Date dimension separately in a calculated member to get your desire results

Check the below query and output

Here customercount is a distinctcount and Intenetsales is a summed amount

```with Member expectedAverge as
[Measures].[Internet Sales Amount] / sumcutomercount

,format_string = '##,###.###'
member sumcutomercount
as
sum( existing
[Date].[Calendar Year].[Calendar Year].members, [Measures].[Customer Count]
)
member normalaverage as
[Measures].[Internet Sales Amount] / [Measures].[Customer Count]
,format_string = '##,###.###'
select {[Measures].[Internet Sales Amount]
,[Measures].[Customer Count]
,sumcutomercount
,normalaverage
,expectedAverge
} on 0,
[Date].[Calendar Year].members on 1

```

Note: Use of Existing is necessary to get the right figure for leaf level members, as this forces the current context. Also you only need to aggregate the leaf members in the sum function to avoid double counting

Regards

Nishar

Wednesday, October 30, 2013 8:16 PM
• I edited the query slightly to fit my cube, but I can't tell if it's working until i have it as a measure in the cube. I don't know how to write the syntax to create a calculated measure as a script command in BIDS. Can you help? I need the measure expectedAverage created.

with Member expectedAverge as
[Measures].[Term Count] / sumcutomercount

,format_string = '##,###.###'
member sumcutomercount
as
sum( existing
[Dim Date].[Month].[Month].members, [Measures].[Total Employee Count]
)
member normalaverage as
[Measures].[Term Count] / [Measures].[Total Employee Count]
,format_string = '##,###.###'
select {[Measures].[Term Count]
,[Measures].[Total Employee Count]
,sumcutomercount
,normalaverage
,expectedAverge
} on 0,
[Dim Date].[Month].members on 1
from [MyCube]

• Edited by Wednesday, October 30, 2013 9:09 PM
Wednesday, October 30, 2013 8:59 PM
• Is the above modified query working ? It should be straight forward to convert this as calculated member . Are you facing any specific problem

Calculated members in the cube have more properties E.g assigning Measure group . So i would suggest it to do by yourself based on the need

You can check the below link for more help.

http://technet.microsoft.com/en-us/library/ms174826(v=sql.90).aspx

Wednesday, October 30, 2013 9:55 PM