(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

There is another type of calculation called a Level Aware Aggregation. The syntax is very similar to a Table Calculation (you use the same functions), but you dont need to use the 'group by' fields in your visual. The result is not an aggregate value - it is alike a subquery that gets joined back to your data at a row level. One way to de-dupe the data at the Invoice ID level is to take the max PartNo per Invoice and return the cost for those rows by not the others. It could look something like this:

ifelse( maxOver(PartNo, [Invoice No] , PRE_AGG) = PartNo, cost, null)

Then drag that into your visual and sum it.

The lack of aggregation inside the maxOver along with the addition of PRE_AGG on the end turns it into a Level Aware Aggregation.

Can check out this blog post for more on LAAs:

Answer 2

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