4 Replies Latest reply: Aug 10, 2016 6:14 AM by S�lvia Ganh�o RSS

    Pivot table with 'others' label

    S�lvia Ganh�o

      Hi,

       

      Imagine this:

       

      II have a table named table1

      - Category

      - Product

      - Quantity

       

      Category          Product          Quantity

           A                         1                    10

           A                         2                    20  

           A                         3                    30

           B                         4                    40

           B                         5                    50      

           B                         6                    60

       

       

      And a table named table 2:

      - Protocol

      - Product

       

      Protocol          Product

           P1                    1

           P2                    2

       

      I want to bluid a pivot table.

      My dimensions are:

      - Category;

      - Product

       

      My measures are:

      - Sum(Quantity)

       

      My filter is going to be Protocol.

       

      So when I select Protocol P1, I want that my Pivot table shows:

                                                  Sum(Quantity)

      Category A     ----------           60

           1                  ---------           10

       

      But I also want to show all the other Products that are associated with Category A, represented with an "Others" label

       

      Example:

                                                   Sum(Quantity)

      Category A     ----------           60

           1                   ---------           10

           Others         ---------           50

       

       

      And if I select Product 1 and 2 the results I want is:

       

                                                   Sum(Quantity)

      Category A     ----------          60

           1                   ---------           10

           2                   --------             20

           Others         ---------            30

       

       

      Is this Possible? How?

       

      Let me know if you need more to understand my problem.

       

      Regards,

      Sílvia

       

       

       

      (PS: this is kinf off related with this Post: Get associate field of selection)