Forum Discussion
Average where not all values are present
I have two columns, one with date-time values rounded to the hour and one an ID that is present at that time. This looks like:
DATETIME ID
2015-01-06 15:00:00 1
2015-01-06 16:00:00 1
2015-01-06 16:00:00 2
2015-01-06 18:00:00 3
2015-01-06 19:00:00 3
I have grouped them per hour and per day. I now want to calculate the average number of ID's present per day. However, because sometimes there are no ID's present, it doesn't count those times as 0. For example when 1 ID is present for half of a day, it will put the average on 1 instead on 0,5. My average is thus way to high. How can I solve this?
The problem is that there is no dataline when no ID is present. I solved it by not using the standard average function, but by calculating the average using the DATEDIFF to calculate the number of DATETIMEs in the interval between MIN(DATETIME) and MAX(DATETIME).
2 Replies
- vanessafvg
Community Champion
Sof what does your data look like when no idea is present?
like so?
DATETIME ID
2015-01-06 15:00:00
you could always create an index column which will present a value for each datetime, in power query add a index column and then do the average on that rather
or you could create a calculated column in dax
if(isblank(id), 0, id)
think the index column might be easiest if you just trying to get a count for each row
you could also do the average of a countrows
what does your measure look like?
The problem is that there is no dataline when no ID is present. I solved it by not using the standard average function, but by calculating the average using the DATEDIFF to calculate the number of DATETIMEs in the interval between MIN(DATETIME) and MAX(DATETIME).
