cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kannanAhammed
Helper IV
Helper IV

Count the average

Hi,

 

We have a ticketing system in sharepoint where each day a number of tickets will be submitted and some one in the office will resolve it and send the update (in a nutshell).

I have the date of the first ticket creation. How do i calculate the average ticket perday? can someone suggest a dax function samples.

Also a ticket will be considered aged ticket if it passed 5 days from the creation date. how will i show of aged ticket count.

1 ACCEPTED SOLUTION
kannanAhammed
Helper IV
Helper IV

I was able to get it to work with the below dax function 

CreatDate = date(2020,03,02)
NoOfDays = DATEDIFF([CreatDate],TODAY(),DAY)
AverageTicketPerDay = DIVIDE(COUNT(Tickets[Id]),[NoOfDays])

View solution in original post

3 REPLIES 3
kannanAhammed
Helper IV
Helper IV

I was able to get it to work with the below dax function 

CreatDate = date(2020,03,02)
NoOfDays = DATEDIFF([CreatDate],TODAY(),DAY)
AverageTicketPerDay = DIVIDE(COUNT(Tickets[Id]),[NoOfDays])

View solution in original post

amitchandak
Super User IV
Super User IV

@kannanAhammed ,

You need to have date without time

example column

 

Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))

 

 

Then try a measure like

 

averageX(Values(Table[Create Date]), calculate(Count(Table[Ticket ID])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks Amitchandak,

I was attemting the below

Measure = AVERAGEX( VALUES(date(2021,03,02)), CALCULATE(COUNT(Tickets[Id])))
gives me an error as "The VALUES function expects a column reference expression or a table reference expression for argument '1'."
 
So I created a new column as below
CreatedDate = date(2021,03,02)
and a measure as below
Measure = AVERAGEX( VALUES(Tickets[CreatedDate]), CALCULATE(COUNT(Tickets[Id])))
 
But it returns the total count 
 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors