SSAS How to aggregate percentage calculated measures

Posted on 2009-04-25
Last Modified: 2016-02-12
Hi Everyone,
I have a cube which contains vehicle movements. I have created a calculated measure in SSAS to give the daily percentage by vehicle for driving time and stopped time.
The calculation looks like this:

([Measures].[Time Driving]) / (DISTINCTCOUNT({[Date].[Date Time]}) * 86400 * DISTINCTCOUNT({[Vehicle].[Vehicle Name]}))

 where  (DISTINCTCOUNT({[Date].[Date Time]}) * 86400 * DISTINCTCOUNT({[Vehicle].[Vehicle Name]}) is the total number of available seconds in the date range for the selected vehicles.
My problem is that my vehicles are in a hierarchy: Region>Depot>Vehicle,   but when I look at the  result of the calculation at any level higher than vehicle, the percentages for each vehicle are added together.
So if we have 2 vehicles in a depot, one of which drives for 6 hours on a specific day, and the other drives for 18 hours on the same day, the calculation correctly gives me 25% and 75% for the individual vehicles, but 100% for the depot (when it should of course be 50%).
Can anyone help me on this one?
Thank You.
Question by:WizzoP
    LVL 8

    Accepted Solution

    You should have 2 more basic measures for these distinct count measures. Then modify the calculated measure as:

    ([Measures].[Time Driving]) / ([Measures].[TimeSeconds] * 86400 * [Measures].[VehiclesCount])
    LVL 22

    Expert Comment

    Why you dont do the calculation before process the cube? Why you dont use a named qury in spite calculation member?
    LVL 8

    Expert Comment

    Are you joking PedroCGD? Distinct measures are hard to implement for all levels on the relational layer.

    Author Comment

    Thanks srnar. This isn't quite what I need, but I think you are on the right track.
    Creating new measures to calculate the number of days in my range and number of vehicles should make the whole thing much simpler.
    Thanks again, Simon.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Mysql not caching queries 4 33
    Software suggestion 12 18
    Defind RTO and PTO for one of db instance 48 39
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    728 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

    17 Experts available now in Live!

    Get 1:1 Help Now