locked
trying to display a pivot table in a matrix form RRS feed

  • Question

  • I successfully created my first power pivot table however, I am having difficulty trying to format the pivot table to display in a certain way.

    This is how my pivot table looks now (this is a potion of it since it is too large to copy and paste all of it):

      Agent Name Values      
      Agent 1 Agent 1 Agent 1 Agent 10 Agent 10
    Months Sum of Handle Ratio Sum of Count of Called Number Sum of Calls Presented Sum of Handle Ratio Sum of Count of Called Number
    7/1/2010 0.086294416 272 3152 0.023477157 74
    8/1/2010 0.083717864 254 3034 0.030982202 94
    9/1/2010 0.096282173 303 3147 0.032729584 103
    10/1/2010 0.123304562 400 3244 0.027127004 88
    11/1/2010 0.107575758 355 3300 0.036060606 119
    12/1/2010 0.122338628 362 2959 0.04866509 144
    1/1/2011 0.10600907 374 3528 0.046201814 163
    2/1/2011 0.104927536 362 3450 0.047246377 163
    3/1/2011 0.109090909 456 4180 0.036842105 154

     

    This is is the format I would like to have the pivot table display: 

       

    Agent 1

    Agent 2

    Agent 3

    Agent 4

    Agent 5

    Agent Totals

    Jan

    Calls Handled

    157

    340

    236

    178

    532

    1443

     

    Calls Presented

    181

    352

    275

    242

    552

    1602

     

    Handle Ratio

    86.74%

    96.59%

    85.82%

    73.55%

    96.38%

    90.07%

    Feb

    Calls Handled

    117

    280

    288

    129

    530

    1344

     

    Calls Presented

    130

    289

    319

    155

    547

    1440

     

    Handle Ratio

    90.00%

    96.89%

    90.28%

    83.23%

    96.89%

    93.33%

    YTD Totals

    Calls Handled

               
     

    Calls Presented

               
     

    Handle Ratio

               

     Is there a way I can do this?

    thanks,

    Nathan


    Nathaniel Shiffman
    Tuesday, July 19, 2011 5:53 PM

Answers

All replies

  • If you want to stack the measures vertically instead of horizontally , then you just need to click drag the measure.

    For total control you can always convert the pivot table to functions..(pivottable tools -> Olap tools -> convert to formulas)


    http://www.contextures.com/xlVideo001.html
    Tuesday, July 19, 2011 11:34 PM
  • Thank you for your reply. Very interesting but I am afraid I still need a ,ittle more clarification.

     

    For your first suggestion, I watched the video and it the pivot table looks like the typical one I always create in a regular pivot mode but in the power pivot table I created there is no Total column to drag the measure too.

    this is what a portion of it looks like:

    Months Agent Name Sum of Count of Called Number Sum of Calls Presented
    7/1/2010 Agent 1 272 3152
    7/1/2010 Agent 2 141 3152
    7/1/2010 Agent 3 214 3152
    7/1/2010 Agent 4 315 3152
    7/1/2010 Agent 5 399 3152
    7/1/2010 Agent 6 205 3152
    7/1/2010 Agent 7 105 3152
    7/1/2010 Agent 8 212 3152
    7/1/2010 Agent 9 278 3152
    7/1/2010 Total 2141 3152
    8/1/2010 Agent 1 254 3034
    8/1/2010 Agent 2 198 3034
    8/1/2010 Agent 3 290 3034

    As for your second suggestion, I was simply able to convert the pivot table to functions with the instructions you provided but I didnt know what to do after that. What can I do with this now?

    Thanks in advance,

    Nathan

     

     


    Nathaniel Shiffman
    Wednesday, July 20, 2011 12:30 AM
  • I now see that byu using Olap tools to convert the table into functions I can rearrange the table into any format I desire, and from what i read the values can even be updated from the tables,  however, this is not a practical solution for me.

    I am astill trying to figure out how to stack the measures vertically. Any further assistance would be appreciated.

    Thanks,

    Nathan

     


    Nathaniel Shiffman
    Wednesday, July 20, 2011 2:27 PM
  • You can open both the Powerpivot field list and the regular old-fashioned pivot table field list at the same time. Using the regular field list you can drag the measures onto rows.

    http://powerpivotpro.com/2010/06/26/powerpivot-field-list-and-excel-field-list-at-same-time/


    • Marked as answer by nmss18 Wednesday, July 20, 2011 8:09 PM
    Wednesday, July 20, 2011 6:54 PM
  • wow.......it really worked... I can't believe it...I have been trying it figure this out for days. I sincerely thank you. The chart now looks exactly the way I want it to.

     

             
        Agent Name    
    Months Values Agent 1 Agent 2 Agent 3
    7/1/2010        
      Sum of Count of Called Number 272 141 214
      Sum of Calls Presented 3152 3152 3152
      Sum of Handle Ratio 8.63% 4.47% 6.79%
    8/1/2010        
      Sum of Count of Called Number 254 198 290
      Sum of Calls Presented 3034 3034 3034
      Sum of Handle Ratio 8.37% 6.53% 9.56%
    9/1/2010        
      Sum of Count of Called Number 303 99 197
      Sum of Calls Presented 3147 3147 3147
      Sum of Handle Ratio 9.63% 3.15% 6.26%

    Thanks again

    Nathan

     


    nmss18
    Wednesday, July 20, 2011 7:08 PM