TOTAL Calculation

[Dafini]

TOTAL(distinct_count({Field_Name})), is not supported, is there any other option to do

[Brett]

Hi @Dafini,
Are you trying to just get the distinct count of your ‘field name’ field?
You can just remove the ‘TOTAL’:

distinct_count({Field_Name})

Let me know if you’re trying to accomplish something different!

distinct_count - Amazon QuickSight

[Dafini]

No, Actually I am trying to find the Total distinct count of a field, since i am trying to use the total in a calculated field

Overall Percentage=(distinct_count({tracking_number})/TOTAL(distinct_count({tracking_number})))*100

[Brett]

Hi @Dafini,
So do you just mean a total of your field, not partitioned by each tracking number? Because using distinct count will give you a total. Based on your example calculation, it looks like you’re trying to use a partitioned number for the numerator and ideally, a non-partitioned total of your ‘tracking number’…is that correct?

[Dafini]

month tracking no_count
jan 500
feb 300
march 250
Total. 1050
this is a sample table, what i am trying do is finding overall percentage for each month by creating a calculated field overall_percentage for example for jan =(500/1050)*100
instead of using total value directly i am trying to apply this formula here TOTAL(distinct_count({tracking_number}). but it says its not supported. can you provide any other option to do this

Calculations for Total

Hi @ravists2002 To divide a field by the total, you can use the QuickSight aggregation function called “TOTAL.” The “TOTAL” function allows you to calculate the sum of a field across all dimensions in a visualization, ignoring any dimension-based filtering or grouping. To calculate the total of the “Marks/score” column for all subjects, you can create a calculated field using the following formula: SUM(Marks) / TOTAL(SUM(Marks)) Here’s how you can create this calculated field: Open your an…

[Brett]

Hi @Dafini,
Thanks for the additional information, understood now!

I recreated this example with sample data in the attached [Arena view](TOTAL Calculation) for you to check out.

Once you open, click on the button in the left panel ‘copy analysis’ so that you can open it up to see the calculated field I created.

I had to start by creating a month field: ‘Order Month’ and then used in ‘Distinct Count Calc’
Note, instead of adding the *100, I would suggest changing the field type to show as percent and aggregation as ‘min’:

Let me know if you have any additional questions.

[Dafini]

@Brett

TOTAL Calculation

Thank you @Brett for your clear solution, i really appreciate it