Pivot Table Calculate Distinct Percentage Share

Hello I am trying to create a pivot table in which one of the value column is the total percentage share across a particular field. But I am facing an issue the percentOfTotal(count({id_field}),[{field_1}]) does not support distinct count and I am not able to find a work around for it.

Currentl I have created the table with field_1,field_2,field_3 as the rows and share which a calculative field (based on the above formula) and count of distinct ids as values.

I am getting this output

But I am expecting the output to be as

Any help on thi would be greatly appreciated.

Thank you

1 Like

Hello @naveedsayed, so I notice in your percent of total calculation, you are using field_1 as your partition field. I am assuming then that your total you are referencing would be all IDs for each variation in field 1. If that is the case, we can maybe work-around the distinct count issue with percentOfTotal by using LAC-W aggregations instead.

distinctCountOver({id_field}, [{field_2}], PRE_AGG)/distinctCountOver({id_field}, [{field_1}], PRE_AGG)

Now, if field 2 and field 3 matter for the partition of the numerator, you could also add field_3 into the partition. I only used field_2 because based on the example data, it looked like that is the field that mattered. I can include some documentation on the distinctCountOver function, but this should give you the values you are looking for. I will mark my response as the solution, but please let me know if you have any remaining questions.