17 Replies Latest reply: Jan 13, 2017 11:15 AM by Digvijay Singh RSS

    Count of Text In Pivot Table Column

    Dan Swinden

      Hello!

       

      In a pivot table, like the below, I want Dimension 1 (highest level of Pivot Table) to be a total of a text string in dimension 2 below

       

      Example

       

      PIZZA HUT, (Count of "Swap")

           BRAND 1, 'MAINTAIN'

           BRAND 2, 'SWAP'

           BRAND 3, 'SWAP'

           BRAND 4, 'SWAP'

           BRAND 5, 'SWAP'

           BRAND 6, 'SWAP'

           BRAND 7, 'SWAP'

           BRAND 8, 'SWAP'

      JEREMYS BAR, (Count of "Swap")

           BRAND 1, 'MAINTAIN'

           BRAND 2, 'SWAP'

           BRAND 3, 'SWAP'

           BRAND 4, 'MAINTAIN'

           BRAND 5, 'SWAP'

           BRAND 6, 'MAINTAIN'

           BRAND 7, 'SWAP'

           BRAND 8, 'SWAP'

       

      So for Pizza Hut, the total count should be 7.  For Jeremy's Bar, it would be 5.

       

      I have tried;

       

      If(Dimensionality()=1,Count('SWAP'))  This returns a value significantly larger than 7 (41 in my case).

       

      If(Dimensionality()=1,Count(Distinct('SWAP'))  This returns 1

       

      Any ideas would be greatly appreciated!