Sum ONLY for distinct rows

[Test_Analytics]

Hi, I have a table like this.

Batch	Student
A	1
A	1
B	2
B	2
C	2
C	2
C	2

I want to show a score card showing 'Sum of students across all distinct batches', i.e. 1 + 2 + 2 = 5. The normal sum function gives me 12. I want to sum only for distinct batches, i.e. A -> 1, B -> 2, C->2.

Is there a way to do this on Looker Dashboard, without having to create a separate pivot/group by table?

Will be grateful for any help.

[dsimeonova]

Hey @Test_Analytics !
Can you share what type of measures you are using?
You can share the LookML as well, in order to give you proper solution.

[sovandy]

Hello dear, I meet this problem too. Please help us

[dsimeonova]

Hey @sovandy !
Please share screenshots or explain a little bit the fields that you are selecting and what is the goal of what you want to achieve.

[g_cavallo]

Hi @dsimeonova, I have a similar question. I have blended data between two tables, something like:

TABLE A

PROJECT ID	Budget
Project A	250.000
Project B	500.000
Project C	750.000

TABLE B

PROJECT ID	Partner
Project A	Partner 1
Project A	Partner 2
Project A	Partner 3
Project B	Partner 2
Project B	Partner 3
Project C	Partner 3

With the blended source, I get duplicated budget rows, so that when I visualize a budget indicator, with no filters, it is a lot higher that the correct. I would definitely need a SUM_DISTINCT function. Could you help me? 

[GavinW]

In the context of LookML, you can do this with the sum_distinct measure.

[dsimeonova]

To confirm with you @g_cavallo , are you using Looker Studio or Looker ?
Because as how you expressed yourself, sounds like the issue that you have is on Looker Studio? Is that correct?

[g_cavallo]

Exactly, I'm trying to get it into Looker Studio. Is that possible? 

[dsimeonova]

I'm afraid that I don't have enough experience on Looker Studio to confirm on that topic.
The other question ( as far as I understand) was related to Looker, which has completely different functionalities than Looker Studio (even though they share common name).

[JMJS]

The simplest option is to create a field with a window operation, for example a row_number for the Batch field and Student, and for the calculation perform the sum of the Student field when the value of the window operation is 1.