Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 ...
Data Protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    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])
amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous ,

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])))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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