Looker View - How to SUM a COUNT_DISTINCT Measure

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 3 6,616
3 REPLIES 3

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?

Hi Izzy,

Thanks for your response!

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”.

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:

Why don't my totals match the values in my table?

Sometimes the display totals feature in Looker does not match the total you expected. Unlike a spreadsheet, Looker totals do not calculate a simple sum over the returned rows or columns in the repo...

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!

Top Labels in this Space
Top Solution Authors