Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Calculated Measure in Cube

Posted on 2013-10-31
Medium Priority
346 Views
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
0
Question by:LauraLynne
• 4
• 4

LVL 66

Assisted Solution

Jim Horn earned 1500 total points
ID: 39614296
>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 Comment

ID: 39614359
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 Comment

ID: 39614388
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

LVL 66

Expert Comment

ID: 39614436
Yep.  I'll back away gracefully now to encourage SQL - SSAS experts to respond.
0

Accepted Solution

LauraLynne earned 0 total points
ID: 39621547
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

LVL 66

Expert Comment

ID: 39621562
Thanks for the accept, and thanks for posting your ultimate solution.

Jim
0

LVL 66

Expert Comment

ID: 39621580
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 Closing Comment

ID: 39635423
We figured it out inhouse.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
###### Suggested Courses
Course of the Month10 days, 18 hours left to enroll