topic Re: Total <Dim1> Count not working if Dim 1 calculated dimension in App Development

Total Count not working if Dim 1 calculated dimension

Sa_Rah — Wed, 18 Oct 2023 16:47:11 GMT

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?

Re: Total Count not working if Dim 1 calculated dimension

vincent_ardiet_ — Wed, 18 Oct 2023 17:11:39 GMT

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])

Re: Total Count not working if Dim 1 calculated dimension

Sa_Rah — Wed, 18 Oct 2023 17:17:51 GMT

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)

Re: Total Count not working if Dim 1 calculated dimension

vincent_ardiet_ — Wed, 18 Oct 2023 17:23:47 GMT

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

Re: Total Count not working if Dim 1 calculated dimension

Sa_Rah — Thu, 19 Oct 2023 09:33:05 GMT

Thank you, that worked perfectly!!