Search code examples
excel-2010mdxolap

Second Ordered set is not ordered

In an Excel OLAP Pivot I have the following custom set called "EventsOrdered":

ORDER(
    {[DimensionEvents].[Events].[Event].members},
    SUM (
            TAIL([Date].[Date - Calendar Month].[Calendar Month].members, 12),
            [Measures].[Revenue]
            ),
    BDESC
)

When I add it as the second column to the pivot I get the following:

enter image description here

What it is ordering the events by is World revenue but I want the order of the events to be partitioned by the country e.g the yellow row should be higher up the order for the US.

Is this possible? - how do I change the MDX script to reflect this context aware behavior in Excel- I've unsuccessfully played around with some tuples that use the CURRENTMEMBER function like the following but no joy:

ORDER(
        {[DimensionEvents].[Events].[Event].members},
    SUM (
           (
             [Date].[Date - Calendar Month].CURRENTMEMBER,
             [Geolocation].[Geography].CURRENTMEMBER
           ),
           [Measures].[Revenue]
           ),
    BDESC
)
Solution

  • did you try the following?:

    ORDER(
    {[DimensionEvents].[Events].[Event].members},
    SUM (
            TAIL([Date].[Date - Calendar Month].[Calendar Month].members, 12),
            [Measures].[Revenue],
            [Geolocation].[Geography].CURRENTMEMBER
            ),
    BDESC
)

    philip,