average of column in table

[andrew_hardwick]

I am trying to display the average of a column within a table. My table contains information has shown below:

The IncidentToTaskTime2 column is a measure which looks at the difference betweeen Incident OpenDateTime and Task OpenDateTime, displayed in hours.

However the total at the bottom displays 1. This is clearly not the sum of that column, so I am not sure what it is displaying. Either way, I would like to show the average of the column there. I cannot see any options for this, can someone help?

[MFelix]

Hi @andrew_hardwick,

When calculating a measure this is based on context so when you add it to a table visual it will calculate based on the line data, when you go to the subtotals/ totals line this is based on the total information so when making averages or max values you will get odd number because it considers the full data.

You need to add a measure like this:

average total line =
IF (
    HASONEVALUE ( Table[Name] );
    [IncidenToTaskTime2];
    AVERAGEX ( ALLSELECTED ( Table[Name] ); [IncidenToTaskTime2] )
)

In this measure I'm overwriting the Incident Measure by another measure that makes the Average line by line of the Incident Measure.

Basically is looking at the data and if it has a name then returns the Measure, if you don't have the name it creates a "virtual table" with all the measure values and then makes the average of those values.

Regards,

MFelix

[andrew_hardwick]

Hi @MFelix

I tried this and it doesn't seem to have worked exactly as expected. I created the measure as below:

average total line =
IF (
    HASONEVALUE(Incident[Name]),
    [IncidentToTaskTime2],
    AVERAGEX ( ALLSELECTED (Incident[name]), [IncidentToTaskTime2] )
)

I then added this to my table and it displays as shown:

Which seems almost correct, but when I check the same values in Excel and do an average I get a different result.

1

1

1

23

36

36

16.33333

So the averge doesn't appear to have calculated correctly.

[MFelix · Accepted solution]

Hi @andrew_hardwick,

Your problem in this measure is that the Name on the first column has a duplicated value so whern calculating average it only get a 36 one time and divides by 5 not 6, is the second name column compose by unique ID's? If so select that column for your measure, or another one that as unique values, even if it's not on your table visual.

Regards,

MFelix

[andrew_hardwick]

Hi @MFelix. Indeed you are correct, that is what the issue is.

Ideally I would only have one Name ID in the first column. The reason it is showing 2 is that there are 2 tasks linked to the unique ID in the first Column. In actual fact, what I would like is to only have the task with the earliest date shown. This would remove the average error and make the report more accurate.

Do you also know how I can limit the results to show the first Task linked to the ID? I have built a relationship between the 2 table using the Unique ID shown in the first Column.

[MFelix]

Try to change the summarization on the column you want to the first / minimum on the table visual.


Regards,
MFelix

[andrew_hardwick]

I tried selecting Name on the first column, but it does not make any difference. It still shows the duplicate entry.

[andrew_hardwick]

Hi @MFelix. I managed to get rid of the duplicate by selecting first on the 3rd column too, however the average still appears to be wrong.

The screenshot below shows the output, but in Excel I calculate the average as 38.85. I added another measure myself which came out at 36.2, but is still not accurate.

IncidentToTaskTime2 average per Ticket_Type__c =
AVERAGEX(Incident,[IncidentToTaskTime2])

The date is shown below.

Any ideas?

First Name	Incident OpenDateTime	First Name	Task OpenDateTime	IncidentToTaskTime2	average total line	IncidentToTaskTime2 average per Ticket_Type__c
1417804	16/09/2018 07:57	98142	16/09/2018 08:33	1	1	1
1417821	16/09/2018 08:46	98143	16/09/2018 09:12	1	1	1
1417848	16/09/2018 10:45	98144	16/09/2018 11:10	1	1	1
1417865	16/09/2018 11:26	98196	17/09/2018 10:22	23	23	23
1417971	17/09/2018 00:46	98162	17/09/2018 04:50	4	4	4
1418041	17/09/2018 02:22	98987	25/09/2018 14:16	204	204	204
1418047	17/09/2018 02:41	98159	17/09/2018 02:42	0	0	0
1418077	17/09/2018 03:26	98311	18/09/2018 03:31	24	24	24
1418081	17/09/2018 03:33	98208	17/09/2018 13:29	10	10	10
1418138	17/09/2018 05:06	98164	17/09/2018 06:26	1	1	1
1418142	17/09/2018 05:09	98181	17/09/2018 08:52	3	3	3
1418216	17/09/2018 06:09	98163	17/09/2018 06:09	0	0	0
1418234	17/09/2018 06:19	98167	17/09/2018 07:24	1	1	1
1418370	17/09/2018 07:31	98183	17/09/2018 09:26	2	2	2
1418372	17/09/2018 07:32	98168	17/09/2018 07:32	0	0	0
1418391	17/09/2018 07:39	98304	18/09/2018 01:36	18	18	18
1418414	17/09/2018 07:46	98185	17/09/2018 09:50	2	2	2
1418416	17/09/2018 07:46	98186	17/09/2018 09:50	2	2	2
1418417	17/09/2018 07:48	98187	17/09/2018 09:50	2	2	2
1418421	17/09/2018 07:49	98188	17/09/2018 09:50	2	2	2
1418425	17/09/2018 07:50	98171	17/09/2018 07:50	0	0	0
1418429	17/09/2018 07:53	98178	17/09/2018 08:28	1	1	1
1418444	17/09/2018 08:03	98331	18/09/2018 08:41	24	24	24
1418445	17/09/2018 08:04	98174	17/09/2018 08:04	0	0	0
1418446	17/09/2018 08:04	98332	18/09/2018 08:42	24	24	24
1418450	17/09/2018 08:06	98333	18/09/2018 08:43	24	24	24
1418452	17/09/2018 08:06	98334	18/09/2018 08:45	24	24	24
1418464	17/09/2018 08:10	98175	17/09/2018 08:12	0	0	0
1418464	17/09/2018 08:10	98548	20/09/2018 09:16	73	73	73
1418477	17/09/2018 08:16	98176	17/09/2018 08:16	0	0	0
1418547	17/09/2018 08:37	98180	17/09/2018 08:37	0	0	0
1418605	17/09/2018 08:58	98454	19/09/2018 11:56	51	51	51
1418609	17/09/2018 09:01	98193	17/09/2018 10:07	1	1	1
1418623	17/09/2018 09:06	98198	17/09/2018 10:52	1	1	1
1418664	17/09/2018 09:22	98197	17/09/2018 10:32	1	1	1
1418674	17/09/2018 09:27	98439	19/09/2018 08:09	47	47	47
1418689	17/09/2018 09:35	98184	17/09/2018 09:39	0	0	0
1418694	17/09/2018 09:40	98306	18/09/2018 02:28	17	17	17
1418727	17/09/2018 10:04	98522	20/09/2018 06:43	68	68	68
1418732	17/09/2018 10:08	98335	18/09/2018 08:46	22	22	22
1418738	17/09/2018 10:13	99303	28/09/2018 02:19	256	256	256
1418751	17/09/2018 10:21	98213	17/09/2018 13:42	3	3	3
1418757	17/09/2018 10:25	98739	21/09/2018 15:15	101	101	101
1418767	17/09/2018 10:28	98336	18/09/2018 08:47	22	22	22
1418836	17/09/2018 11:11	99650	03/10/2018 06:27	379	379	379
1418842	17/09/2018 11:19	98555	20/09/2018 10:40	71	71	71
1418876	17/09/2018 11:40	98344	18/09/2018 09:55	22	22	22
1418888	17/09/2018 11:49	98372	18/09/2018 15:20	28	28	28
1418903	17/09/2018 11:58	98199	17/09/2018 12:19	1	1	1
1418911	17/09/2018 12:02	98952	25/09/2018 07:45	187	187	187
1419020	17/09/2018 13:10	98207	17/09/2018 13:10	0	0	0
1419193	17/09/2018 14:30	98220	17/09/2018 14:32	0	0	0
1419218	17/09/2018 14:42	98229	17/09/2018 15:44	1	1	1
1419248	17/09/2018 14:57	98984	25/09/2018 13:55	191	191	191
1419280	17/09/2018 15:25	98316	18/09/2018 06:05	15	15	15
1419313	17/09/2018 15:46	98702	21/09/2018 07:33	88	88	88
1419323	17/09/2018 15:51	98541	20/09/2018 08:37	65	65	65
1419343	17/09/2018 16:16	98230	17/09/2018 16:20	0	0	0
1419356	17/09/2018 16:33	98547	20/09/2018 09:03	65	65	65

[MFelix]

Hi @andrew_hardwick,

Is it possible to share a sample of the PBIX file, maybe by private message?

Regards,

MFelix