# Percentof Total with distinct count

Hello, I want to count the percent of total with aggregating {mức thu nhập tích luỹ}, but it turns into errors

I’d like to turn this calculated field into percent for adding a percent line on this visual:

distinct_countIf({Transactions Publisher}, truncDate(‘DD’, {publishers_ctime}) <= truncDate(‘DD’, \${Dateto}) AND truncDate(‘DD’, {transactions_sales_time}) <= truncDate(‘DD’, \${Dateto}) AND truncDate(‘DD’, {Latest_transactions_sales_time}) <= addDateTime(-\${DaysWihtOutTransaction}, ‘DD’, \${Datefrom}))

=> My percentoftotal calculated field below seems incorrect:

percentOfTotal(avg(distinctCountOver(ifelse(truncDate(‘DD’, {publishers_ctime}) <= truncDate(‘DD’, \${Dateto}) AND truncDate(‘DD’, {transactions_sales_time}) <= truncDate(‘DD’, \${Dateto}) AND truncDate(‘DD’, {Latest_transactions_sales_time}) <= addDateTime(-\${DaysWihtOutTransaction}, ‘DD’, \${Datefrom}), {Transactions Publisher}, null), [{mức thu nhập tích luỹ}], PRE_AGG)))

Hi @thecuon119
Is it possible to share a sample data for this?

Regards
Vetri

Hello @thecuon119, I think to get the solution you are looking for you will want to break this up into 2 fields.

Rather than using distinct_countIf, create a calculated field ifelse statement that will return the `Transactions Publisher` field or NULL. I’ll call it `Transactions Calc` to reference.

Then you can make a second calculated field where you use the result of the ifelse statement to manually make the percentage:
`distinct_count({Transactions Calc})/sum({mức thu nhập tích luỹ})`

Or if the distinct count and sum need to be partitioned you can do this:
`distinctCountOver({Transactions Calc}, [{Partition Field}])/sumOver({mức thu nhập tích luỹ}, [{Partition Field}])`

When you are trying to use different types of aggregations to get your expected result, it is often better to change the method to avoid aggregation errors. I hope this helps lead you to a working solution!

1 Like