(Quicksight) How to Sum Values only from Unique Fields

Question

My data set has a {Invoice No} field that I want to sum the cost of. All invoice numbers are unique and have one cost value. However, because there are multiple {Part No} fields in each {Invoice No}, Quicksight multiplies this one cost by however many part numbers there are in each invoice, so the sum ends up being some multiple of the real value that I want. This is what the table view looks like:

{Invoice No}    {Part No}     {cost}
12345            001           10.12
12345            002           10.12
12345            003           10.12

How can I just use the first {cost} value of each {Invoice No}, so it doesn't duplicate it? I thought a workaround could be creating a new field that omits any duplicate invoice numbers, but don't know how to do that in Quicksight. Any solution would be much appreciated.

Answer 1

I can get this to work in a table by using a calculated field.

The calculation I am using is

sumOver(max(cost),[{Invoice No}])

Which basically states, grouped by {Invoice No} take a max of the values in the cost field (max is arbitrary since they're all the same value). Then sum the results.

Unfortunately this didn't seem to work in a KPI card (probably nicest bet for just showing a sum). It yielded the error Table calculation attribute reference(s) are missing in field wells.

To test this, I expanded your data set a bit

Invoice No,Part No,cost
12345,001,10.12
12345,002,10.12
12345,003,10.12
12346,001,42.42
12346,003,42.42
12347,0032,3.01
12348,0033,.04

Then displayed the value in a table, including sum