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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Monthly Average of Each Day's Count

Hi, I'm having a dataset like this:

111.png

 

 

I want to calculate the average of each day's count. Since the count for the same is the same, I want each day's count be only calculated once.

Here is what I want:

Average = (100+200+300+400) / 31

Divided by 31 because there are 31 days in May.

 

Is it possible to achieve this by writing measures? 

Any help is appreciated.

2 ACCEPTED SOLUTIONS
ryan_mayu
Super User
Super User

@Anonymous 

 

you can try this

Measure = 
VAR _sum=SUMX(SUMMARIZE('Table','Table'[date],'Table'[count]),'Table'[count])
VAR _days=DATEDIFF(min('Table'[date]),EDATE(min('Table'[date]),1),DAY) 
RETURN _sum/_days

1.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

amitchandak
Super User
Super User

@Anonymous , Try one of the two

Averagex(summarize(Table,table[Date],"_1",[count]),[_1]) // Or use sum(Table[count])

Averagex(values(table[Date]),[count]) // Or use sum(Table[count])

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@Anonymous , Try one of the two

Averagex(summarize(Table,table[Date],"_1",[count]),[_1]) // Or use sum(Table[count])

Averagex(values(table[Date]),[count]) // Or use sum(Table[count])
ryan_mayu
Super User
Super User

@Anonymous 

 

you can try this

Measure = 
VAR _sum=SUMX(SUMMARIZE('Table','Table'[date],'Table'[count]),'Table'[count])
VAR _days=DATEDIFF(min('Table'[date]),EDATE(min('Table'[date]),1),DAY) 
RETURN _sum/_days

1.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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