Percent of total for a distinct count and calcualte as group down

Question & Answer
,
1

Hi,
I only see percent of total table calculation for aggregate as count but not in distinct count in a pivot table. I want % of total grouped by {Deployment Status}
Is there a work around to achieve this? I used something like this but results are not right
distinctCountOver({Serial Id}, [{Deployment Status}], PRE_AGG)/distinctCountOver({Serial Id}, , PRE_AGG)

2

Hi @bethasb

Your approach seems right. Can you give some details of the output you get and what your desired output is so we can review and comment?

Regards
Giri

3

Thanks for the reply.

image
image909×546 46.4 KB

For region APMEA Cluster AKL I want the defect percent of total to be 7/11*100 = 63.63.
But when I use the calculated field it says invalid syntax.

image
image909×546 3.99 KB

4

Hi @bethasb,
It’s been awhile since last communication on this thread, were you able to find a work around or are you still encountering issues?

Were you receiving an invalid syntax error from this calculated field that you tried?
distinctCountOver({Serial Id}, [{Deployment Status}], PRE_AGG)/distinctCountOver({Serial Id}, [] , PRE_AGG)

Or were you just receiving incorrect results? The syntax seems accurate on that so if you were receiving incorrect results, I wonder if it has something to do with you not partitioning by your date field as well? Maybe try adding that.

In regards to the percentage, instead of adding the *100, try adding in the table calculation on it’s own:

image
image348×290 22 KB

Let us know if you have any additional questions, if we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!