Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Could you try to write your dimension like this:
$(=pick($(vDimension1), 'Division', 'Specialty', 'StGr'))
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])
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)
Could you try to write your dimension like this:
$(=pick($(vDimension1), 'Division', 'Specialty', 'StGr'))
Thank you, that worked perfectly!!