PowerPivot 2010 sorting & total? RRS feed

  • Question

  • Hi, I have SQL 2008 R2 table as below :

    Ledger      Date                    Account            DailyAmount        MonthlyAmount
    LED1      2011-08-15     Purchase Acc       80.00                     500.00
    LED1      2011-08-15     Bank Acc             71.95                     450.25
    LED1      2011-08-15     Reconcile Acc       100.08                   896.22
    LED1      2011-08-15     Maintain Acc        90.20                     412.00
    LED2      2011-08-15     Zero Balance Acc   5.00                        20.56
    LED2      2011-08-15     Bank Acc             76.95                      45.00
    LED2      2011-08-15     Purchase Acc        23.61                     89.25

    Question 1) Below is my SQL table and if user drag-and-drop "Account" colmun, I don't want them to sort by i.e. A-to-Z. It should be default sorting same as above table has.

    Question 2) When user drag-and-drop "DailyAmount" column as measure against Account and Date then finla TOTAL coulumn should be the FIRST value from MonthlyAmount colmun. As default behaviour tt should not do SUM of DailyAmount in TOTAL column.

    Any idea how to achieve this in PowerPivot?  Thanks.

    • Edited by KM IT Wednesday, September 21, 2011 6:32 AM
    Friday, September 16, 2011 12:26 AM


  • Hi KM IT,

    To your first question "I don't want them to sort by i.e. A-to-Z. It should be default sorting same as above table has."

    By default in powerpivot, the column is sorted by alphabet, if you want to customize the sorting, you can create another column with ID number, then you can sort your Account column by ID number column.

    To your second question, if I understand you correctly, you want to lay MonthlyAmount column on TOTAL coulumn, actually TOTAL coulumn is created automatically, we canot lay MonthlyAmount column on TOTAL coulumn.

    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Challen Fu Sunday, September 25, 2011 8:22 AM
    Wednesday, September 21, 2011 9:31 AM