cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nazish
Helper I
Helper I

How to calculate Average per type per month and year

Hi Team,

I have below data columns (Issue number, RespTimeMins, month and year) in Table 1 and (Issue number and Issue Type) columns in Table 2. I want to calculate Average of Response Time by Type per month and year. Can you please help me how I can get this?

Table 1

Nazish_2-1660661337539.png

Table 2

Nazish_3-1660661450933.png

Thankyou,

16 REPLIES 16
Nazish
Helper I
Helper I

Hello Burno, 

So I am looking for Average of RespTimeMins as per Issue Type - Monthly and Yearly. below is expected output:

 

Nazish_0-1660663011127.png

Hi again, 

Are the tabel 1 connected to table 2 ? 

if yes, creat a simple average ( Avg Time = Average('table1'[Avg Response time]) ) and pull the atribute as the exemple that you have send. 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BC

Hi, I tried but it didnt work. It gives the total average for all issue types which I dont want. I want seperate Average Response time for each issue.

please check below screenshot.

Nazish_0-1660665065495.png

 

Hi again, 

Are the tabel 1 connected to table 2 ? 

Can you share the relationship between the two tables.

 

BR 

Bruno Costa

Hi @onurbmiguel_ ,

 

They are in same report in power BI, under same report  and I can link them to each other.

please check below screenshot.

Nazish_0-1660830264257.png

 

Hi again, 

I need to understang the model, 

can you share how you connect the two tabels between them? 

go to model and see the relationship: 

onurbmiguel__0-1660833150415.png

 

onurbmiguel__1-1660833206011.png

 

BR 

Bruno

@onurbmiguel_ here it is;

 

Nazish_0-1660833519328.png

 

Hi again, 

You need to creat a relationship from TotalTimeOpenCust to the others tabels , try to use the IssueNbr.

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BC

Hi @onurbmiguel_ I have created the relationship between all the tables. Can you help me with Average Syntax now !!

Thanks.

I am not using this table for this report actually. so i think that will work. but can you share he revised syntax for calculating Average as per type please.

Thanks.

Hello again, 

If the relationship between the tables are correctly , you can use the previous measures:

onurbmiguel__0-1660854723690.png

 

if not please send me a pbix with dummy values so i can help you. 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BC

Hi @onurbmiguel_ thanks, but this syntax is giving the total average, which I dont want.  I am looking for individual average for each Issue Type seperately. for example, 

For Jan;

Average of RespTimeMinutes of IT Admintration 

Average of RespTimeMinutes of IT Services 

Average of RespTimeMinutes of Networking 

Hello Nazish,

Send me a pbix with dummy values so i can take a look. 
A need to see the data to understand.

 

BR 

Bruno 

hi @onurbmiguel_ can you reply to my pbix i shared. I am waiting for the responce.

thanks,

Hi @onurbmiguel_ please find link of the pbix below. let me know if you can edit and have access to it?

https://app.powerbigov.us/groups/me/dashboards/d336dc7a-2a94-455e-8780-b61ccf547ea5?ctid=ed14db84-f9... 

onurbmiguel_
Solution Supplier
Solution Supplier

Hello Nazish, 

What should be the intended result?

Can you simulated the output ?

 

BR
Bruno Costa

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.