none
Excel MODE function - value that occurs the most RRS feed

  • Question

  • Hi

    I am looking for an implementation of the EXCEL MODE function for Analysis Services calculation. MEDIAN is available as integrated function, but MODE is missing.

    Has someone of you an MDX implementation for this ? (for an Sum Measure) (- I am concerned about the performance if I do this over >50.000 Member Sets manually.)

    Best Regards

    HANNES

    Monday, August 7, 2006 9:55 AM

Answers

All replies

  • I have programmed my one procedure the do the job.

     

    If you are interested the code is available at http://www.hmayer.net/tiki-list_file_gallery.php?galleryId=24

     

    HANNES

    Friday, June 22, 2007 8:56 PM
  • Interesting problem, Hannes. It is possible in MDX - here's a solution:

     

    Code Snippet

    with

    member measures.countsame as

    count(

    filter(union([Date].[Calendar].currentmember.level.members, {[Date].[Calendar].currentmember} as currentmonth)

    , ([Date].[Calendar].currentmember, [Measures].[Customer Count])

    = (currentmonth.item(0).item(0), [Measures].[Customer Count])

    )

    )

    member measures.mode as

    (topcount([Date].[Calendar].[Month].members,1, measures.countsame).item(0).item(0), [Measures].[Customer Count])

    select {[Measures].[Customer Count], measures.countsame, measures.mode} on 0,

    [Date].[Calendar].[Month].members on 1

    from [Adventure Works]

     

     

    Although I suspect there might be a more a efficient way of doing it in MDX (I need to think a bit), custom code may well give you the best performance. If you could test this against your procedure I'd be interested to hear the result!

     

    Regards,

     

    Chris

    • Proposed as answer by Ajaymdx Monday, December 21, 2009 12:27 AM
    Monday, June 25, 2007 8:53 AM
    Moderator