Learn how to a build a cloud-first strategyRegister Now

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

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
Asked:
WizzoP
  • 2
1 Solution
 
srnarCommented:
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
 
PedroCGDCommented:
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
 
srnarCommented:
Are you joking PedroCGD? Distinct measures are hard to implement for all levels on the relational layer.
0
 
WizzoPAuthor 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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