How to create a chart that shows the average tickets per month?

[zpaul_010]

Apologies, Im kinda new to PowerBi. 

I have a 2 tables, 1 has all the ticket details like ticket #s, Teams, created date, etc while the 2nd table has months and work days. Have yet to touch the programming part of powerbi yet. 

I need to show a line chart showing the monthly average of daily tickets we're passing to the telephony team from Jan - Jun this year.

So instead of showing lets say, 562 tickets in a month, I'd like to show 563 tickets in June (table1) divide by 22 working days (table 2)  25.54 tickets per day are sent to the telephony team 

[V-pazhen-msft]

@zpaul_010 

1. Create a calculate column to find the working days

Weekdays = If(weekday([date],2)>6, 1)

2. Then you just use the sum of the ticket by month divided by the sum of the weekdays in the month. 

Average = Calculate(sum([tickets),Allexcept(Table, [Date].[Month]) / Calculate(sum([workingdays),Allexcept(Table, [Date].[Month])

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zpaul_010]

@ V-pazhen-msft

Thanks for the response. Im a bit struggling... 

I tried to make it simple so I tried to write a simple measure:

AverageDay = DISTINCTCOUNT(Sheet1[Ticket ID])

/ 'sheet2'[WorkDaysInAMonth]

Unfortunately, the 'sheet2'[WorkDaysInAMonth] which is in another table is not showing at all after the "/"

[V-pazhen-msft · Accepted solution]

@zpaul_010 

For columns from other tables, you could use Related() function to call column from another table. 

AverageDay = DISTINCTCOUNT(Sheet1[Ticket ID]) 
/ Related('sheet2'[WorkDaysInAMonth])

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@zpaul_010 - This is going to depend very much on your data model. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

All that said, this might help: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

[amitchandak]

@zpaul_010 , create a date table with working days and sum up those and divide it by sales

workday = if( WEEKDAY([Date],2)<6,1,0)

example

sum(sales[sales])/sum(date[workday ])

[zpaul_010]

Thanks for the response. Im a bit struggling... 

I tried to make it simple so I tried to write a simple measure:

AverageDay = DISTINCTCOUNT(Sheet1[Ticket ID])

/ 'sheet2'[WorkDaysInAMonth]

Unfortunately, the 'sheet2'[WorkDaysInAMonth] which is in another table is not showing at all after the "/"