Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI tried selecting Name on the first column, but it does not make any difference. It still shows the duplicate entry.
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 |
Hi @andrew_hardwick,
Is it possible to share a sample of the PBIX file, maybe by private message?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |