Looker View - How to SUM a COUNT_DISTINCT Measure

Looker Q&A Looker
,
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"
 }

}

lookerissue.jpg
lookerissue.jpg1366×768 50.9 KB

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

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?

3

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

rowtotals.png
rowtotals.png1366×768 69.9 KB

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

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!