# Calculated Measure in Cube

Hi. Use to belong to Experts-Exchange for years and now have re-subscribed.
I am trying to create a calculated measure in a cube for an average.

What I want it to do is first determine if the count of services is 0 then 0
else divide the total turn around days by the count of services. My turn around time
is based on certain statuses.  I included a field in my status table to indicate if it is one of the statuses that the turn around time is calculated on.

WITH MEMBER [Measures].[AverageTurnAroundTime] AS

[Measures].[Turn Around Days]/[Measures].[SAR Services]
select [Measures].[AverageTurnAroundTime] on columns
from [SAR Management]
where [Sar Status Code].[Turn Around Time Indicator] = 1
###### Who is Participating?

Author Commented:
We were able to find the answer in house. I am posting the solution. I am awarding jimhorn points for helping with the divide by zero logic. Thanks.

WITH MEMBER [Measures].[AverageTurnAroundDays] AS
IIF([Measures].[Turn Around Days]= 0,0,[Measures].[Turn Around Days]/[Measures].[SAR Services])
select {[Measures].[Turn Around Days],[Measures].[SAR Services],[Measures].[AverageTurnAroundDays] } ON COLUMNS,
[Initial Submit Date].[Month].&[2013-10-01T00:00:00]ON ROWS
from [SAR Management]
where [Sar Status Code].[Turn Around Time Indicator].&[1]
0

Microsoft SQL Server Developer, Architect, and AuthorCommented:
>first determine if the count of services is 0 then 0
>else divide the total turn around days by the count of services

Can't help you with any SSAS aspects of this question, but raw T-SQL would be <air code>..
``````SELECT CASE
WHEN COALESCE(COUNT(services),0) > 0 THEN 0
ELSE SUM(Turn around days) / CAST(COUNT(Services) as numeric(19,4))  END as column_name
FROM [SAR Management]
WHERE [Sar Status Code].[Turn Around Time Indicator] = 1
``````
btw the CAST..as Numeric is so that the result has decimal values.
0

Author Commented:
Appreciate the help, jimhorn! Still need the solution for the cube average though. Changed the logic above to read

case when COALESCE(COUNT(services),0) = 0 THEN 0 rather than > 0.

Now I just have to figure out how that code gets modified to MDX. Thanks
0

Author Commented:
I will be awarding points for your help, btw, because it is helping me in my solution. Still seeking assistance for the cube calculation. Laura
0

Microsoft SQL Server Developer, Architect, and AuthorCommented:
Yep.  I'll back away gracefully now to encourage SQL - SSAS experts to respond.
0

Microsoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the accept, and thanks for posting your ultimate solution.

Jim
0

Microsoft SQL Server Developer, Architect, and AuthorCommented:
btw I've noticed that there's not a lot of us SSIS experts on EE, and probably fewer SSAS experts, so you can probably score a lot of points by answering questions here.
0

Author Commented:
We figured it out inhouse.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.