Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 4718

# SSAS How to aggregate percentage calculated measures

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
WizzoP
• 2
1 Solution

Commented:
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

Commented:
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

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

Author Commented:
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

• 2
Tackle projects and never again get stuck behind a technical roadblock.