Averages with multi-select

I am trying to write an MDX calculation that calculates the average policy premium across multiple policies. It works fine when selecting one carrier. However, the avg function is basically based on the count and sum functions (at least that is what I read). When you select one carrier the average is based on the following:

Policy A: 2000

Policy B: 1000

Policy C: 3000

Average: 2000

Lets say I multi-select another carrier and the second carrier has the exact same amounts for the policies

the total policy amounts would be :

Policy A: 4000

Policy B: 2000

Policy C: 6000

Average: 2000

The summation (12000) should be divided by the number of policies * the number of carriers (6).

However, I wind up getting 4000 as my average. Any thoughts?

Here is the MDX I am using:

Total Policy Premiums:

sum(existing [POLICY].[POLICY #],([POLICY].[VEHICLE #].&[1],[measures].[TOTAL POLICY PREM]))

Avg:

avg(existing [POLICY].[Policy].children,[Measures].[Total Policy Premiums])

Any insight would be greatly appreciated.

You could do this for example:

AVG ( Existing CrossJoin( [Carrier].[Carrier].members, [Policy].[Policy #].members ),

[Measures].[TOTAL POLICY PREM] )

Average can be computed in multiple ways, so you need to think through on what exactly you are computing. In the MDX above, combinations of carrier and policy that do not exist or are null will not be included in the computation.

Average Turnaround Time - Datediff() Help

Hi everyone, I need some help with creating a report that calculates the average turnaround time in days that it takes for units to return from trips destined to a location.

The database that I am working with lists a trip each time a unit is dispatched to a destination, and then another trip is created for the units return. In the example below I am trying to calculate the number of days that it takes for a unit to return to Vancouver by calculating the difference between the departure date from Vancouver and the arrival date back into Vancouver. I then need to calculate the average number of days that it takes for a unit to return from a trip. See sample data below.

UNIT -- TRIP -- START LOCATION --START DATE--FIN LOCATION--FIN DATE
================================================== =======
U12 --001 -- VANCOUVER ---FEB 10 -- ONTARIO -- FEB 15
U10 --002 -- VANCOUVER ---FEB 13 -- ONTARIO -- FEB 18
U12 --003 -- ONTARIO ----MARCH 13 -- VANCOUVER -- MARCH 18
U10 --004 -- ONTARIO ----MARCH 1 -- VANCOUVER --MARCH 6

Unit U12 took 36 days to return back to Vancouver
Unit U10 took 21 days to return back to Vancouver

Therefore based on the two trips it takes an average of aproximately 28.5 days for a unit to return from trips destined to Ontario.try this, not a greate SQL though :)

select Route, avg(DaysTaken) as AvgDaysTaken
from (
select A.Unit,(select top 1 Start_Location from MyTable where MyTable.Unit=A.Unit order by Trip)+ '-'+(select top 1 Fin_Location from MyTable where MyTable.Unit=A.Unit order by Trip)+'-'+(select top 1 Start_Location from MyTable where MyTable.Unit=A.Unit order by Trip) as Route, cast(A.DaysTaken as decimal(9,2)) as DaysTaken
from (
select Unit,datediff(d,min(Start_date),max(fin_date)) DaysTaken from MyTable group by unit
) A
) B
group by Route