none
PowerPivot - How to display two fields in one row

    Question

  • Hi ALL,

    I'm trying to build power pivot report that can looks like the below example

    ItemGroup ItemCode ItemName Sales

    ---------------------------------

    Fruit            A101       Apple         $100

    Fruit            A102        Pear          $200

    Fruit            A103        Banana      $300

    Vege            B101        Carrot        $20

    Vege            B102        Pumpkin      $10

    I also want to group by "ItemGroup" so users can expand each group and see items in it. Problem is if I use normal pivot table it groups by ItemName in addition to ItemCode.  I want PP to consider ItemCode and name as one and group it together. Is this possible?

    Workaround is create a new field in model concatenating code and name. I don't want to use a flat table as it doesn't group by itemGroup.

    Any Ideas?

    Thanks

    Sonny

    Friday, January 24, 2014 12:25 AM

Answers

  • I think your work around of concatenating the two fields together is the only way to achieve this behaviour. PowerPivot does not have the concept of "dimension properties" or 1-1 relationships which would enable the sort of behaviour that you are after.

    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by vr.babu Tuesday, January 28, 2014 5:13 AM
    • Marked as answer by sonnyA Tuesday, January 28, 2014 7:45 AM
    Tuesday, January 28, 2014 5:00 AM
    Answerer

All replies

  • maybe im oversimplifying this but you can simply remove ItemCode and ItemName from your pivot table and it should group by ItemGroup automatically???


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, January 27, 2014 9:36 PM
    Answerer
  • Thanks. It does group by item Group now, problem is displaying both item code and Item Name when you expand Item group
    Monday, January 27, 2014 9:42 PM
  • I think your work around of concatenating the two fields together is the only way to achieve this behaviour. PowerPivot does not have the concept of "dimension properties" or 1-1 relationships which would enable the sort of behaviour that you are after.

    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by vr.babu Tuesday, January 28, 2014 5:13 AM
    • Marked as answer by sonnyA Tuesday, January 28, 2014 7:45 AM
    Tuesday, January 28, 2014 5:00 AM
    Answerer