none
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 Djaber T 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 ?

    Thnks in advance 

      
    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
  • @Guoxiong

    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,

    Thanks for your question.

    >>>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
    Moderator
  • Hi willson yuan

    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)

    Could you help me please



    • Edited by Djaber T 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


    Please vote as helpful or mark as answer, if it helps


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

    Thanks for your response.

    If you have created this measure as I posted. The way it calculate is get the countofsession for day 1 to day 7, with you sample data, it should be 100+55+110+150+80+200+220 = 915, the CountOfVehiclesfor day 1 to day 7 is 1000+1100+1100+1150+1300+1350+1300 = 8300, the logic is getting 915/8300 = 0.11 , and 0.11 is the right answer you are after. In the opposite, summing or averaging averages is not mathematically correct.


    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

    Friday, July 13, 2018 4:44 AM
    Moderator