Forum Discussion
average per hour /last days
Hi All.
I'm trying to get the average revenue per hour from my fact table, but only from the last 60 days
i can get the sum with a dax like that:
CALCULATE(SUM(Fact[Amount]),Date[Date] >= TODAY()-60)
But, it's just a sum..
When i'm pivoting this measure per hour, i have the total per hour.
and if i use the AVERAGE, i get the average per hour, but without the sum of hours from all of the days.
How can i get the measure?
Example:
Date Hour Value
2017-05-01 00 15
2017-05-01 00 52
2017-05-01 00 150
2017-05-02 00 10
2017-05-03 00 20
2017-05-04 00 25
2017-05-05 00 50
To get the avg of 00:00, first I need to sum all of facts that happened at 00:00 for each day, and then perform the avg among the days.
So, (15+52+150) = 217
avg(217+10+20+25+50) = 64.4
Thanks
Hi lcampos，
After research, use the sum measure to divide the distinct dates. Please create a measure using the formula.DistinctDates = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(Fact,Date[DATE]>=Today()-60),"Date",Sales[DATE])))
Average=DIVIDE(CALCULATE(SUM(Fact[Amount]),Date[Date] >= TODAY()-60),Fact[DistinctDates])If you still have other problem, please share more details for further analysis.
Best Regards,
Angelia
3 Replies
- Greg_Deckler
Community Champion
Take a look at my article here:
https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
You want to group by SUM and then have super groups of AVERAGE.
v-huizhn-msftMicrosoft Employee
Hi lcampos，
After research, use the sum measure to divide the distinct dates. Please create a measure using the formula.DistinctDates = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(Fact,Date[DATE]>=Today()-60),"Date",Sales[DATE])))
Average=DIVIDE(CALCULATE(SUM(Fact[Amount]),Date[Date] >= TODAY()-60),Fact[DistinctDates])If you still have other problem, please share more details for further analysis.
Best Regards,
Angelia- v-huizhn-msft
Hi lcampos,
Have you resolved your problem? Please mark the helpful reply as answer, or share your solution. More people will find workaround clearly and easily.
Best Regards,
Angelia
