How to sum distinct record

[aijac1314]

Hi everyone, I try very hard to figure out the formula to achieve this result, but I am failed.

Your help is really appreciated.

I got a table, which contains a key and a number value. What I want to achieve is, sum up the quantity and listing with distinct key, eg:

Table:

ST001, 5

ST002, 10

ST001, 3

ST003, 4

ST002, 1

Return result:

ST001, 8

ST002, 11

ST003, 4

[Marc_Dillon]

The answer depends on where you're wanting to show this info.

In a view? I'd use a Slice that only returns a single record per key, and a VC that calculates the sum.

In a Bot report? You can set up a START expression that iterates through only the unique keys, and a SUM(SELECT within it to output the sums per key.

[aijac1314]

Thanks for the prompt reply, I trying to understand your suggestion, but I can't get a clue about it.

I actually would like to display it in a view.

Let's say I use a slice, which returns a single record per key (maybe I can use UNIQUE()?), what if returns a single record per key with the condition?

Anywhere, thanks alot.

[Marc_Dillon]

The method to reduce a table to unique values on a [column], in a Slice, looks like this:

[_THISROW] = MAXROW( table , _RowNumber , [column] = [_THISROW].[column] )

Then to aggregate a [2nd-column] by that [column], in a VC, would be like this:

SUM( SELECT( table[2nd-column] , [column=[_THISROW].[column] ) )

[aijac1314]

Thank you very much, this help alot!!

😍