Total <Dim1> Count not working if Dim 1 calculated dimension

[Sa_Rah]

Hi

The below formula works fine in a pivot table if Division is a regular (non calculated) dimension:

count ( total <Division> distinct [EmpNo])

However if Division is a calculated dimension selected using the pick formula below, the above stops calculating correctly and instead returns the grand total rather than the total over just the relevant Division:

=pick($(vDimension1), Division, Specialty, StGr)

(Note that vDimension1 resolves to a number 1, 2 or 3.)

Any idea why this might be happening, or what the solution should be?

[vincent_ardiet_]

You can try with:

count ( total <$(=pick($(vDimension1), Division, Specialty, StGr))> distinct [EmpNo])

Or if your calculated dimension is the first one of your object:

count ( total <$(=GetObjectDimension(0))> distinct [EmpNo])

[Sa_Rah]

Thanks. I did try this whilst attempting to figure this out myself, but unfortunately anything other than a regular dimension doesn't seem to work within the Total <>

(again apologies for the automatic accepting a solution and the like, it's due to security checks on links performed by my organisation's email provider)

[vincent_ardiet_ · Accepted solution]

Could you try to write your dimension like this:
$(=pick($(vDimension1), 'Division', 'Specialty', 'StGr'))

[Sa_Rah]

Thank you, that worked perfectly!!