Quick calculation based on bar chart

[Anonymous]

Dear all,

I have a dataset containing > 2MM rows of historical data (2019 - YTD). It is very easy to make a barchart displaying monthly / daily totals of a certain colomn. E.g. the count of a certain column on dec 3rd is 149 and on dec 12th is 108. I would like to display the hourly average of a certain column per day: so for dec 3rd this would be 149/24=6,2 and for dec 12th this would be 108/24=4,5. Because I have so many data points the PowerBI file cannot finish a dax expression to obtain these hourly average values per day. Is there an easy way to show these calculated numbers?

If more info is needed I'm happy to provide,

Thanks,

Lucas

[amitchandak · Accepted solution]

@Anonymous , One of the ways is to create an hour column or date with only out and divide by that.

New column

Hour = hour(Table[Datetime])

or

Date hour = [datetime].date + hour(Table[Datetime])

Measure

Divide(sum(Table[Column]),distinctcount(Table[Hour]))

or

Divide(sum(Table[Column]),distinctcount(Table[Date Hour]))

[Anonymous]

Thanks for the quick reply, it works!

[Anonymous]

Hello @Anonymous ,
You can try a calculated column.
Can you please provide some sample data so can check?
Thanks