Post reply

How to sort the SUM calculation

  • March 25, 2012 at 11:39 pm

    #259076

    Hi,

    I have created a calculation to add the SalesRank% (it is a calculation as well) cumulatively.

    SUM

    (

    {[Inventory].[Item Code].CurrentMember.Level.Members}.Item(0):[Item Code].CurrentMember

    ,

    [Measures].[SalesRank%]

    )

    But the problem is when I connect to the SSAS via Excel and sort those data by SalesRank% value, the CumRank% value is still calculated and sorted by Item Code.

    e.g.

    Item Code CumRank% SalesRank%

    1 1% 1%

    2 5% 4%

    3 10% 5%

    4 12% 2%

    Is there a way to add the SalesRank% value cumulatively and users can see the CumRank% value based on SalesRank% not Item Code? (should be shown in the beneath)

    Item Code CumRank% SalesRank%

    3 5% 5%

    2 9% 4%

    4 11% 2%

    1 12% 1%

    Thanks in advanced!

  • March 27, 2012 at 7:50 am

    #1464985

    you can use the excel sort functionality.

  • March 27, 2012 at 5:50 pm

    #1465418

    Hi Scott,

    Thanks for your response. Is there a way to do it in SSAS?

  • March 27, 2012 at 6:11 pm

    #1465419

    Not for a measure... you could do some MDX, but that would be dependend on the other dimensions selected. Again, sorting is for the presentation layer, ie your Excel Pivot. Also, once the sort is in place in Excel, that pivot sort will remain.

  • March 27, 2012 at 8:55 pm

    #1465440

    Thanks for the clarification, Scott:)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply