How to show percentage of the Average time in another column in Matrix

[annie_liu]

So I want to show the percentage for the average Open/Closed time in a Matrix table base on another field but with the return of additional calculations.

So I found the Average of Time Percentage by calculating  the field [Open/Closed Time] within my table by making a custom column t to get the Average of Time Percentage field within this table of (([#"Open/Closed Time"]*8)/(2000))) else 0) to get that percentage however I want to show that in a percentage  but it's showing me this within the Matrix instead of what I would like to see that show up in Excel and the column total should show the average total but it's wrong.

[milanpasschier3]

Hey,

I will look into this tomorrow! Can you drop a link of your dummy data?

Best,

Milan

[annie_liu]

I can give you a sample of the data here..  

closed_at	opened_at	Group Manager	Open/Closed Time
2/14/2021 19:00	2/14/2021 17:35	A	0.06
2/21/2021 19:00	2/7/2021 9:29	A	14.40
10/2/2021 22:30	9/19/2021 0:38	B	13.91
5/18/2021 16:30	5/17/2021 21:59	B	0.77
5/17/2021 20:00	5/17/2021 18:33	B	0.06
8/15/2021 20:00	7/30/2021 11:11	C	16.37
8/4/2021 3:47	8/2/2021 0:30	C	2.14

Here's what the result of the matrix supposes to look like..using Dax measure.. I want to see the average time percentage using this calculation. 

[FreemanZ · Accepted solution]

hi @annie_liu 

not sure if i fully get you. try to plot a visual with the manager column and two measures like:

Measure1 = AVERAGEX(data, data[closed_at]-data[opened_at])

Measure2 = 
DIVIDE(
    AVERAGEX(
        data,
        data[closed_at] - data[opened_at]
    ),
    (2000/8)        
)

it worked like:

[annie_liu]

This works however, how do I deal with the subtotal for each column?

[FreemanZ]

hi @annie_liu 

What do you expect to have as the column subtotal?

[annie_liu]

It would be the average of each column - 7.13 and 2.

[milanpasschier3]

Hey man, can you deliver it in an excel file or pbix file with the date column formatted in a date format instead of text?

[milanpasschier3]

Check the screenshot too:

[milanpasschier3]

Hey man,

Fixed it! See: https://drive.google.com/file/d/1TatnfvlNMyJ6ly7qkBBaE-taq9c_c6-c/view?usp=sharing

If you would like to download the excel file too, see: https://docs.google.com/spreadsheets/d/193YsYUoSXvO2fBtIrffoEq7Ogrx3aciQ/edit?usp=sharing&ouid=10835...

Best,

Milan

[annie_liu]

@milanpasschier3  

 I didn't include my Excel sheet for this to show I got the average of the fields to get the percentage here's what the fields of the Excel sheet sample look like I took the average of the Open/Closed time for each manager...so now I try to get the percentage by taking the average of the Open/Closed time *8 hrs divide by 2000 hrs a year and multiple by 100 to get the percentage..so I try to show that using DAX measures but it's not working.