Average using text column

[BI_Analyticz]

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]

Hi  @BI_Analyticz  ,

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.

[amitchandak · Accepted solution]

@BI_Analyticz ,

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

[Anonymous]

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! 

[BI_Analyticz]

Thanks Amit worked like a charm !

[BI_Analyticz]

@amitchandak @mahoneypat 

any ideas pls