Search code examples
sql-serverdatabasedata-visualizationtableau-apidata-warehouse

Tableau Q: Calculating Average from Duplicate Rows

I have this data warehouse with table structure like this. I want to create visualization using tableau for average customer points per year.

CustomerID  ,Order      ,CustomerPoints ,OrderYear
A123        ,Table      ,450        ,2014
A123        ,Chair      ,450        ,2014
B236        ,Mouse      ,500        ,2014
B236        ,Keyboard   ,500        ,2014
B236        ,Monitor    ,500        ,2014
C135        ,Cabinet    ,600        ,2014
D246        ,Lamp       ,400        ,2014

If I do average straight away it will count 7 rows instead of 4 distinct row.

This is how it should be calculated CustomerID ,CustomerPoints ,OrderYear A123 ,450 ,2014 B236 ,500 ,2014 C135 ,600 ,2014 D246 ,400 ,2014 OrderYear, AverageCustomerPoints 2014, 487.5

CustomerPoints from each of customerID/count customerID (4 people)

I tried count distinct per customer ID and using {Fixed ([CustomerID]) : Average([CustomerPoints])} but the result is different when I tried to do it manually using different table.

Is there any way to do this strictly using tableau calculation/feature for this?

Solution

  • Helped by someone AVG({Fixed ([CustomerID]) : AVG([CustomerPoints])})