topic Re: How to show percentage of the Average time in another column in Matrix in DAX Commands and Tips

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

annie_liu — Tue, 02 May 2023 14:07:59 GMT

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.

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

milanpasschier3 — Tue, 02 May 2023 15:15:41 GMT

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=108354620094745256212&rtpof=true&sd=true

Best,

Milan

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

milanpasschier3 — Tue, 02 May 2023 15:13:04 GMT

Check the screenshot too:

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

annie_liu — Tue, 02 May 2023 16:45:15 GMT

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.

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

milanpasschier3 — Tue, 02 May 2023 17:56:16 GMT

Hey,

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

Best,

Milan

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

annie_liu — Tue, 02 May 2023 19:58:45 GMT

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.

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

milanpasschier3 — Wed, 03 May 2023 10:58:09 GMT

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?

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

FreemanZ — Wed, 03 May 2023 11:50:53 GMT

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:

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

annie_liu — Wed, 03 May 2023 13:12:06 GMT

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

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

FreemanZ — Wed, 03 May 2023 13:20:31 GMT

hi annie_liu

What do you expect to have as the column subtotal?

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

annie_liu — Wed, 03 May 2023 20:46:05 GMT

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