cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PoojaG Frequent Visitor
Frequent Visitor

Average Message per hour

Hello experts,

 

I am trying to calculate Average message per hour.  There were couple of similar post but none worked as per my requirement. Seems to be straight forward but not really. It can be better explained with an example.

I have a datetime table with datetime column and Hour column . Each date will have 24 rows determined by the Hour column starting from 0 to 23 as there are 24 hours in a day. Hour column is a text field as I don't want it to aggregate.  If the date range is selected from 1st May 2019 to 5th may 2019, the total hours (

SUM(DateTime[rowcount])) for each Hour column would be 5. I have another field named Count of Post from Posts table. It's a simple dax measure DISTINCTCOUNT(Posts[post_id]). 
Now, to calculate an Average message per hour, all I did was created dax column 
Avg message per hour = DIVIDE([Count of Post],[total hours],0)
 I also tried the below formula: Attached is the result set which is not correct. Please help!
Avg hour = 
VAR HOURCREATION= 
FIRSTNONBLANK(DateTime[Hour],DateTime[Hour])
RETURN
DIVIDE(
CALCULATE(COUNTROWS(Posts),ALLEXCEPT(DateTime,DateTime[Hour])),
COUNTROWS(
SUMMARIZE(
FILTER(ALL(DateTime),DateTime[Hour] = HOURCREATION),
DateTime[Hour])
)
)avg message per hour.PNG
1 ACCEPTED SOLUTION

Accepted Solutions
PoojaG Frequent Visitor
Frequent Visitor

Re: Average Message per hour

I was able to get the desired results with the below DAX:

DIVIDE(CALCULATE(SUM(Posts[post count])),CALCULATE(SUM(DateTime[rowcount])),0)

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Average Message per hour

@PoojaG ,

 

"Measure * Measure" and "Measure / Measure" will cause complex issue. I would suggest you to convert measures [total hours] and [Count of Post] to calculate columns and then create measure using them.

 

Regards,

Jimmy Tao

PoojaG Frequent Visitor
Frequent Visitor

Re: Average Message per hour

@v-yuta-msft  I converted the measure into calculated columns and then averged them, it still doesn't give me correct results.

PoojaG Frequent Visitor
Frequent Visitor

Re: Average Message per hour

I was able to get the desired results with the below DAX:

DIVIDE(CALCULATE(SUM(Posts[post count])),CALCULATE(SUM(DateTime[rowcount])),0)

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 319 members 3,325 guests
Please welcome our newest community members: