Question

Looker View - How to SUM a COUNT_DISTINCT Measure

  • 7 May 2019
  • 3 replies
  • 4501 views

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


3 replies

Userlevel 7
Badge +1

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

Userlevel 7
Badge +1

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!

Reply