Solved

# SSAS How to aggregate percentage calculated measures

Posted on 2009-04-25
4,428 Views
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.
0
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])
0

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?
regards,
Pedro
www.pedrocgd.blogspot.com
0

LVL 8

Expert Comment

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

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.
0

## Featured Post

### Suggested Solutions

PERFORMANCE OF SQL QUERY 13 45
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.