# How can i specify the type of aggregation in calculated member (puting avg instead of sum).

### Question

• Hello everyone,

I'm a beginner in SSAS and i want to know how can get the average X value / Y value. so just to explain the context:

I have dimension vehicles and dimension session and also measures Number fo sessions and number of vehicles.

I want to know the average of sessions per vehicle, the result that i had per day is good but when i roll up  i get the sum of days values.

How can i specify the type of aggregation in calculated member (put avg instead of sum).

Best regards

• Edited by Tuesday, July 10, 2018 3:55 PM
Tuesday, July 10, 2018 3:49 PM

### All replies

• It depends on your fact table how to store both key values of the vehicles and sessions. Can you provide the details?

A Fan of SSIS, SSRS and SSAS

Tuesday, July 10, 2018 4:04 PM
• Hi @Guoxiong

yes In the fact table i have the two keys and in the measures i have count of sessions and count of vehicles

Is it clean ?

Tuesday, July 10, 2018 4:12 PM
• When you said "... count of sessions and count of vehicles", the counts are based on what? daily, monthly or something?

A Fan of SSIS, SSRS and SSAS

Tuesday, July 10, 2018 4:17 PM
• Yes daily  the small level

Tuesday, July 10, 2018 4:25 PM
• I guess that you have the date column for those counts. So you need to do the calculation like Sum(Count_Of_Sessions) / Sum(Count_Of_Vehicles) grouping by date.

A Fan of SSIS, SSRS and SSAS

Tuesday, July 10, 2018 4:51 PM
• Hi Djaber,

>>>yes In the fact table i have the two keys and in the measures i have count of sessions and count of vehicles
Assuming you have created these two measure called CountOfSessions and CountOfVehicles.

>>>I want to know the average of sessions per vehicle, the result that i had per day is good but when i roll up  i get the sum of days values.
In this scenario, you can just try below MDX code:

```Create Member CurrentCube.[Measures].[Average]
As [Measures].[CountOfSessions]
/
[Measures].[CountOfVehicles];```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Wednesday, July 11, 2018 6:04 AM
• It was exactly what i did but i had the problem,  so to, explain better i give you the example  bellow for one week:

Day 1 20/07/2018 i have CountOfSessions=100, CountOfVehicles = 1000      CountOfSessions/CountOfVehicles  = 0.1

Day 2 21/07/2018 i have CountOfSessions=55, CountOfVehicles = 1100   CountOfSessions/CountOfVehicles  = 0.05

Day 3 22/07/2018 i have CountOfSessions=110, CountOfVehicles = 1100   CountOfSessions/CountOfVehicles  = 0.1

Day 4 23/07/2018 i have CountOfSessions=150, CountOfVehicles = 1150   CountOfSessions/CountOfVehicles  = 0.13

Day 5 24/07/2018 i have CountOfSessions=80, CountOfVehicles = 1300   CountOfSessions/CountOfVehicles  = 0.06

Day 6 25/07/2018 i have CountOfSessions=200, CountOfVehicles = 1350   CountOfSessions/CountOfVehicles  = 0.15

Day 7 26/07/2018 i have CountOfSessions=220, CountOfVehicles = 1300   CountOfSessions/CountOfVehicles  = 0.17

this is the analys per day with the mdx that i did which is the same you sent me

but when i rollup to week i got (0.1 + 0.05 + 0.1 + 0.13 + 0.06 + 0.15 + 0.17) = 0.76

the logic is getting 0.76/7 (avg of the avg)

• Edited by Thursday, July 12, 2018 3:53 PM
Thursday, July 12, 2018 3:50 PM
• Hi Djaber,

Assuming you have a date hierarchy, you can try something like this

Create Member CurrentCube.[Measures].[Average]

As

CASE WHEN ISLEAF([Date].[Week Hierarchy].CURRENTMEMBER) THEN

[Measures].[CountOfSessions]
/
[Measures].[CountOfVehicles];

ELSE

SUM({[Date].[Week Hierarchy].CURRENTMEMBER.CHILDREN},[Measures].[CountOfSessions] )

/

SUM({[Date].[Week Hierarchy].CURRENTMEMBER.CHILDREN},[Measures].[CountOfVehicles] )

PS: If you have multiple hierarchies then you can scope this average measure across different hierarchies.

HTH,

Ram

Thursday, July 12, 2018 8:24 PM
• Hi Djaber T,