Count of Text In Pivot Table Column

[#1]

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!

[sunny_talwar]

Listing Status looks more like an expression then a field from the script?

[#3]

Yes sorry it is.  It is a long if statement.  Anything at dimension level 3 will be evaluated on 3 criteria and return 1 of 3 responses.

my eventual goal is;

Dimensional Level 1 (615 ABN SR Southwest Edmonton) to be a Sum of Dimension level 2 (Canadian Brewhouse).

And Dimension 2 (Canadian Brewhouse) to be a count of "Swap to Powerplay" below

[sunny_talwar]

Can you share the expression you are using for Listing Status?

[#5]

Right now i have it so that Level 1 and 2 of Dimensionality return a placeholder 'Open To Review'.  Thats what i am trying to solve with this.

if(dimensionality()=1,'Open To Review',

if(dimensionality()=2,'Open To Review',

if(Dimensionality()=3,

if("R3M Volume"/3=0,"",

if(("R3M Volume"/3)<1,'Swap To Powerplay',

if(("R3M Volume"/3)<2,'Potential Swap',

if(("R3M Volume"/3)>2,'Maintain')))))))

[sunny_talwar]

How about this?

If(Dimensionality()=1, Sum(If("R3M Volume"/3 < 1, 1, 0)))

[#7]

I don't think that would work in this case as it would only work of the sum for Dimension 1 is lower than 1, as opposed to a count of all the Dimension level 3 that are meeting that criteria.  I appreciate your help though!

[sunny_talwar]

I might be able to help better if I have a sample to look at, but without one, I just understand the problem in bits and pieces

[Digvijay_Singh]

Not sure but you may need two measures to achieve your output, see if something like this can help, I used expressions here as suggested by stalwar1