# Looker View - How to SUM a COUNT_DISTINCT Measure

(Austin Scholl) #1

Hello fellow Lookers, I have a View file that contains a Measure to COUNT_DISTINCT Id’s.

I apply this Measure to the following Dimension:

``````dimension: monetary {

case: {
when: {
sql: \${TABLE}.monetary = 5 ;;
label: "High"
}
when: {
sql: \${TABLE}.monetary = 4 ;;
label: "High/Medium"
}
when: {
sql: \${TABLE}.monetary = 3 ;;
label: "Medium"
}
when: {
sql: \${TABLE}.monetary = 2 ;;
label: "Medium/Low"
}
when: {
sql: \${TABLE}.monetary = 1 ;;
label: "Low"
}
else: "unknown"
}
``````

}

`````` measure: at_risk_strategic {
type: count_distinct
sql: \${shopper_id} ;;
filters: {
field: monetary
value: "High,High/Medium"
}
``````

}

As you can (hopefully) see, the at_risk_strategic Measure returns 2 different values, one count for “High” and one count for “High/Medium”. I then need to SUM these two counts, however, I am informed by Looker that I cannot perform aggregations like SUM on Measures.

Any help is solving this problem is greatly appreciated

0 Likes

(Izzy) #2

It looks like you’re looking for a ‘Row total’— You’re right that we can’t sum measures, since that would be performing a measure of a measure which makes SQL start feeling iffy. However, you’ll notice there’s a “Row Totals” button on that explore near the calculations button. Turning that on will generate a row total which will be the total of both High/Medium and High!

That’s by far the easiest way to do it. Does that work?

0 Likes

(Austin Scholl) #3

Hi Izzy,

I tried calculating the “Row Totals” using the method you outlined. However, my totals are not correct. See the image below

Correct me if I’m wrong, but I thought using the “Row Totals” would add the “337,856” and “277,824”. Instead it’s adding “332,484” and “283,196”.

0 Likes

(Izzy) #4

Hey Austin, sorry to reply late to this one. This is a common confusion with the way that Looker generates totals— There’s actually a whole help center article about it:

Basically, the totals that Looker creates aren’t really the visual sum of the data displayed in the results table— Rather, a similar query is run again without the same grouping, which can lead to discrepancies, especially when there are duplicated values. Give that article a read and see if it clarifies things!

0 Likes