newbie dax question - how to count the number of rows that have the same date?

[sossie07]

Hi all,

I have a newbie dax/BI question,

I have a weather station that takes the temperature every hour (approximatly). What I want to do is find the average temperature for the day.

To do this, I could sum the max temp for the day, and the min and devide by two, but this is not very accurate average, and I have the temperature taken every hour. I could add up all the temperature readings for the day, and assume there are 24 measurements, but I have noticed sometimes its out by a minute or two, so perhaps sometimes there are 23 measurements.

So what I would like to do is sum the temperature readings for a day, and then devide this by the count of readings for the day.

I have two questions around this -

1) Would it be best to make this a new column or a measure?

2) can anyone give a sample of how I might calculate this?

Thanks

[ribisht17 · Accepted solution]

@sossie07 

1. Measure -https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

2. Your measure should be  DIVIDE( SUM(Temperature), Count_Hours  )

where Count_Hours =CALCULATE(COUNT(Orders[Order Date]),ALLEXCEPT(Orders,Orders[Order Date]))

It will count the number of occurrences within a day, assuming Order Date granular level is set to a day,

Please post sample data to get more clarifications

Regards,

Ritesh

[tamerj1 · Accepted solution]

Hi @sossie07 
Measure = AVERAGE ( 'Table'[Column] )

[v-tangjie-msft · Accepted solution]

Hi @sossie07 ,

According to your description, you want to find the average temperature for the day. Here are my steps you can follow as a solution.

(1)This is my test data.

(2)We can create  a measure.

Average temperature = DIVIDE(CALCULATE(SUM('Table'[Temperature]),ALLEXCEPT('Table','Table'[Date])),COUNTROWS(ALLEXCEPT('Table','Table'[Date])))

(3)Then the result is as follows.

If this method does not meet your needs, you can provide us with detailed input and output examples in tabular form so that we can better solve the problem for you.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[tamerj1 · Accepted solution]

Hi @sossie07 
Measure = AVERAGE ( 'Table'[Column] )

[ribisht17 · Accepted solution]

@sossie07 

1. Measure -https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

2. Your measure should be  DIVIDE( SUM(Temperature), Count_Hours  )

where Count_Hours =CALCULATE(COUNT(Orders[Order Date]),ALLEXCEPT(Orders,Orders[Order Date]))

It will count the number of occurrences within a day, assuming Order Date granular level is set to a day,

Please post sample data to get more clarifications

Regards,

Ritesh