SSAS Calculated Measure for % time spent

Posted on 2009-04-23
Last Modified: 2016-02-12
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.
Question by:WizzoP
    1 Comment
    LVL 14

    Accepted Solution

    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.PercentageSales 
    MEASURES.[Unit Sales])',
    FORMAT_STRING = '#.00%'SELECT {MEASURES.[Unit Sales],     MEASURES.PercentageSales} ON COLUMNS,
    NON EMPTY {[Store].[Store City].MEMBERS} 
    FROM [Sales] 

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now