(Quicksight)如何仅对唯一字段中的值求和 [英] (Quicksight) How to Sum Values only from Unique Fields

问题描述

我的数据集中有一个{Invoice No}字段，我想对其总计.所有发票编号都是唯一的，并且具有一个成本值.但是，由于每个{Invoice No}中都有多个{Part No}字段，因此Quicksight将此成本乘以每个发票中存在的许多零件号，因此总和最终是我想要的实际值的倍数.表格视图如下所示:

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

如何仅使用每个{Invoice No}的第一个{cost}值，所以它不重复呢?我认为一种解决方法可能是创建一个新字段，该字段会忽略任何重复的发票编号，但是不知道如何在Quicksight中执行此操作.任何解决方案将不胜感激.

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.

推荐答案

我可以通过使用计算所得的字段使它在表中工作.

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}])

基本上说，按 {Invoice No} 分组的成本字段中的值的 max ( max 是任意的，因为它们都是相同的值).然后对结果求和.

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.

不幸的是，这似乎不适用于KPI卡(这可能是只显示一笔总和的最佳选择).产生错误表计算属性参考在字段井中丢失.

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

这篇关于(Quicksight)如何仅对唯一字段中的值求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！