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
LauraLynneAsked:
Who is Participating?
 
LauraLynneConnect With a Mentor 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
 
Jim HornConnect With a Mentor 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 

Open in new window

btw the CAST..as Numeric is so that the result has decimal values.
0
 
LauraLynneAuthor 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LauraLynneAuthor 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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yep.  I'll back away gracefully now to encourage SQL - SSAS experts to respond.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the accept, and thanks for posting your ultimate solution.
Good luck with your project.

Jim
0
 
Jim HornMicrosoft 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
 
LauraLynneAuthor 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.

All Courses

From novice to tech pro — start learning today.