average per hour /last days

[lcampos]

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

[v-huizhn-msft · Accepted solution]

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

[Greg_Deckler]

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.