Forum Discussion
Average using text column
Hi I have 2 columns date and ticket number. Date column is in datetime format and ticket number is a text column since the ticket numbers are like AB123, AB345, etc.,
I want to show the count of tickets every year or month or date in a drill down graph which is very much possible.. But how to show the average for a year or month or individual dates in the drill down graph. Since the ticket number field is a text I dont know how to proceed.
DATE Ticket Number
1-1-2018 AB123
1-1-2018 AB124
1-1-2018 AB174
2-1-2018 AB123
2-1-2018 AB123
.
.
1-10-2020 AB1211
17-10-2020 AB12334
17-1-2020 AB123666
.
Anonymous ,
For Average, you need to count till some place. Like count till date and then take daily average for month year
averageX(values(Table[date]), calculate(count(Table[Ticket Number])))
In case you have month and year in a date table
averageX(values(date[date]), calculate(count(Table[Ticket Number])))or Avg of month till year(Sum till month)
averageX(values(date[Month Year]), calculate(count(Table[Ticket Number])))
5 Replies
any ideas pls
Anonymous ,
For Average, you need to count till some place. Like count till date and then take daily average for month year
averageX(values(Table[date]), calculate(count(Table[Ticket Number])))
In case you have month and year in a date table
averageX(values(date[date]), calculate(count(Table[Ticket Number])))or Avg of month till year(Sum till month)
averageX(values(date[Month Year]), calculate(count(Table[Ticket Number])))
Hi Amit, do you mind explaining how the suggested measure works? I have a similar issue on my end where I'm trying to find the average number of occurences in a text column. I belive your posted solution will work for my case scenario, but I don't fully understand the logic.
Why do you have to include a date column?
Thank you!
Hi Anonymous ,
show the count of tickets every year or month or date in a drill down graph：
You can use the matrix to place the date column in rows and the ticket number in values
Result:
For text calculation: AVG cannot be performed on text. You can try to count the 'table' [ticket number] first, and then perform an AVG
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.