Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 (
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]) ) )
Solved! Go to Solution.
I was able to get the desired results with the below DAX:
DIVIDE(CALCULATE(SUM(Posts[post count])),CALCULATE(SUM(DateTime[rowcount])),0)
I was able to get the desired results with the below DAX:
DIVIDE(CALCULATE(SUM(Posts[post count])),CALCULATE(SUM(DateTime[rowcount])),0)
@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
@v-yuta-msft I converted the measure into calculated columns and then averged them, it still doesn't give me correct results.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |