Crazy average value on measure

Looker is generating a crazy average SQL and I don't understand why. 

In my view, I have a number field which records assignment grades for students:

  dimension: step_grade {
    type: number
    sql: ${TABLE}.step_grade ;;
  }
 

This field is correctly populated in BigQuery with numbers only. It's a FLOAT column. 

a06004de-886c-470e-a768-318272951b83.png

When I query it for average, it returns properly: 

89b2d856-2fee-4c4e-9730-02aff4ac35e5.png

So, I created a measure in the same view to bring out the average value for this field: 

 measure: average_step_grade {
    type: average
    sql:  ${TABLE}.step_grade ;;
    value_format: "0.00\%"
  }

Sometimes it works well. Other times, it gives me a non-sense result. 

dbee5327-ba48-426c-b2a6-a52640746779.png

I noticed that it generates a crazy query for this field. 

(ROUND(COALESCE(CAST( ( SUM(DISTINCT (CAST(ROUND(COALESCE( enrollments.step_grade  ,0)*(1/1000*1.0), 9) AS NUMERIC) + (cast(cast(concat('0x', substr(to_hex(md5(CAST( enrollments.enrollment_id   AS STRING))), 1, 15)) as int64) as numeric) * 4294967296 + cast(cast(concat('0x', substr(to_hex(md5(CAST( enrollments.enrollment_id   AS STRING))), 16, 8)) as int64) as numeric)) * 0.000000001 )) - SUM(DISTINCT (cast(cast(concat('0x', substr(to_hex(md5(CAST( enrollments.enrollment_id   AS STRING))), 1, 15)) as int64) as numeric) * 4294967296 + cast(cast(concat('0x', substr(to_hex(md5(CAST( enrollments.enrollment_id   AS STRING))), 16, 8)) as int64) as numeric)) * 0.000000001) )  / (1/1000*1.0) AS FLOAT64), 0), 6) / NULLIF(CAST(COUNT(DISTINCT CASE WHEN   enrollments.step_grade   IS NOT NULL THEN  enrollments.enrollment_id   ELSE NULL END) AS FLOAT64), 0.0)) AS enrollments_average_step_grade

In my understanding, it should just be AVG(field_name).

The whole calculation is wrong and I am not sure how to fix this. 

Any ideas?

0 3 751
3 REPLIES 3

n_davis
Participant V

I’d check your joins to this table in your explore. Is this the primary table in the explore?

IanT
Participant V

I'm also having this issue. Average of a binary sometimes correctly equals the rate, 71% or w/e, and other times is 3.2354614402792E+19 or somehow even negative -3.99141920340004E+19

Top Labels in this Space
Top Solution Authors