Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lcampos
Frequent Visitor

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

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
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

View solution in original post

3 REPLIES 3
v-huizhn-msft
Employee
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

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
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.