• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

SSAS Calculated Measure for % time spent

Hi Everyone,
I'm fairly new to SSAS and MDX, and I'm having trouble with a calculated measure in SSAS.

I am recording periods of driving time for a fleet of vehicles,  and have measures which record when vehicles are moving and stopped.
I have dimensions for Date and Vehicle (which has a hierarchy to allow me to group vehicles by region, country etc)

Now, for a given date range and subset of vehicles,  I can easily  see the total time spent driving, but what I really want to do is calculate the % of time spent driving (i.e the utilisation of my vehicles).

As a trivial example, suppose I have just 2 vehicles, one of which drives for exactly 6 hours per day over a 5 day period, and the other drives for 18 hours per day over the same period. What I want is a calculation which returns 0.5 (50%) when I query my 2 vehicles for the 5 days period.
0
WizzoP
Asked:
WizzoP
1 Solution
 
wolfman007Commented:
In constructing MDX statements, it is often necessary to relate a current member value to others in the cube hierarchy. MDX has many methods that can be applied to a member to traverse this hierarchy. The most commonly used ones are PREVMEMBER, CURRENTMEMBER, and PARENT. Using these methods, it is easy to derive an MDX statement that calculates the percentage of sales within a city as a percentage of the state's sales:  

WITH MEMBER MEASURES.PercentageSalesAS '([Store].CURRENTMEMBER,MEASURES.[Unit Sales]) / ([Store].CURRENTMEMBER.PARENT,MEASURES.[Unit Sales])',FORMAT_STRING = '#.00%'SELECT {MEASURES.[Unit Sales],     MEASURES.PercentageSales} ON COLUMNS,NON EMPTY {[Store].[Store City].MEMBERS} ON ROWSFROM [Sales]

from

http://www.tek-tips.com/viewthread.cfm?qid=582545&page=36

http://www.microsoft.com/msj/0899/mdx/mdx.aspx
WITH MEMBER MEASURES.PercentageSales 
AS '([Store].CURRENTMEMBER,
MEASURES.[Unit Sales]) / ([Store].CURRENTMEMBER.PARENT,
MEASURES.[Unit Sales])',
FORMAT_STRING = '#.00%'SELECT {MEASURES.[Unit Sales],     MEASURES.PercentageSales} ON COLUMNS,
NON EMPTY {[Store].[Store City].MEMBERS} 
ON ROWS
FROM [Sales] 

Open in new window

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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now