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.
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.
Hello dear, I meet this problem too. Please help us
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.
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.
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?
In the context of LookML, you can do this with the sum_distinct measure.
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?
Exactly, I’m trying to get it into Looker Studio. Is that possible?
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).