none
Semi-additive measure to find an average based on various dimension attributes

    Question

  • I need what i think is a type of semi-additive measure that works like below.

    For the month of October:


    The total line should sum rather than average (using the Avg function would return a total of 15 avg people).

    The People column is what i'm having trouble with. My fact table has a column that is getting me the # of people by Month, Name, and I have it as a cube measure of Distinct Count. I'm unsure of how to write the mdx to get the behavior above.


    • Edited by kayluhh Wednesday, October 30, 2013 5:10 PM
    Wednesday, October 30, 2013 5:07 PM

All replies

  • Confusing, can you elobarate a bit what you are getting now and what you want to achive and your column of interest

    Regards

    Nishar

    Wednesday, October 30, 2013 5:31 PM
  • I have a fact table that looks like this:

    Count is a sum measure and People is a distinct count measure in the cube. I need to create a calculation that takes Count/People. However, I need it to work in this manner:

    For the first record (A), the average should be 1/10. For the second (B), it should be 4/20. Since the calculation is currently Count/People, for the Total line (A and B combined) it will give me 5/15. However, I want the total row to calculate such that it uses the sum of people for A and B, giving me 5/30.

    Wednesday, October 30, 2013 6:04 PM
  • Great, You need to sum the count based on the Date dimension separately in a calculated member to get your desire results

    Check the below query and output

    Here customercount is a distinctcount and Intenetsales is a summed amount

    with Member expectedAverge as
     [Measures].[Internet Sales Amount] / sumcutomercount
    
    ,format_string = '##,###.###'
    member sumcutomercount
    as
    sum( existing
    [Date].[Calendar Year].[Calendar Year].members, [Measures].[Customer Count]
        )
    member normalaverage as
    [Measures].[Internet Sales Amount] / [Measures].[Customer Count]
    ,format_string = '##,###.###'
    select {[Measures].[Internet Sales Amount]
            ,[Measures].[Customer Count]
            ,sumcutomercount
            ,normalaverage
            ,expectedAverge
           } on 0,
            [Date].[Calendar Year].members on 1
    from [Adventure Works]
    
    

    Note: Use of Existing is necessary to get the right figure for leaf level members, as this forces the current context. Also you only need to aggregate the leaf members in the sum function to avoid double counting

    Regards

    Nishar

    Wednesday, October 30, 2013 8:16 PM
  • I edited the query slightly to fit my cube, but I can't tell if it's working until i have it as a measure in the cube. I don't know how to write the syntax to create a calculated measure as a script command in BIDS. Can you help? I need the measure expectedAverage created.

    with Member expectedAverge as
     [Measures].[Term Count] / sumcutomercount

    ,format_string = '##,###.###'
    member sumcutomercount
    as
    sum( existing
    [Dim Date].[Month].[Month].members, [Measures].[Total Employee Count]
        )
    member normalaverage as
    [Measures].[Term Count] / [Measures].[Total Employee Count]
    ,format_string = '##,###.###'
    select {[Measures].[Term Count]
            ,[Measures].[Total Employee Count]
            ,sumcutomercount
            ,normalaverage
            ,expectedAverge
           } on 0,
            [Dim Date].[Month].members on 1
    from [MyCube]



    • Edited by kayluhh Wednesday, October 30, 2013 9:09 PM
    Wednesday, October 30, 2013 8:59 PM
  • Is the above modified query working ? It should be straight forward to convert this as calculated member . Are you facing any specific problem

    Calculated members in the cube have more properties E.g assigning Measure group . So i would suggest it to do by yourself based on the need

    You can check the below link for more help. 

    http://technet.microsoft.com/en-us/library/ms174826(v=sql.90).aspx


    Wednesday, October 30, 2013 9:55 PM