Issues on null results of count

[StilHopen]

In trying to perform some use analysis including minimums/averages/maxes of counts per hour.

I have a table set to group by hour of the day and filtered to a single day of the week along with a single name.  When drilling down this far I see chances at hours where the count can be 0 for that hour, but because it is being filtered it does not consider that 0 count.  Here is some example data(it would be coming from sql)

ID#	Name	DateTime		DateTable[DateTime]
101	orange	1/1/2022 2:00		1/1/2022 1:00
102	black	1/1/2022 3:00		1/1/2022 2:00
103	blue	1/1/2022 2:00		1/1/2022 3:00
104	orange	1/1/2022 3:00		1/1/2022 4:00
105	orange	1/1/2022 3:00		1/1/2022 5:00
106	orange	1/8/2022 2:00		1/1/2022 6:00
107	black	1/8/2022 3:00		1/1/2022 7:00
108	blue	1/8/2022 3:00		1/1/2022 8:00
109	orange	1/15/2022 3:00		1/1/2022 9:00

For this data I would expect for Saturdays For Orange at 3am:

Min 0

Max 2

Average 1

Instead because it doesn't include the count of 0 I see

Min 1

Max 2

Average 1.5

My formulas look like:

AVERAGEX(KEEPFILTERS(VALUES('DateTable'[DateTime])),

CALCULATE(COUNTA('Table'[ID#])))

I am curious if there is a better way to get to what I am looking for.

[StilHopen · Accepted solution]

I am not going to mark this as my answer incase someone else comes up with a better answer, but what I did was to fix min I have it doing a date diff between the max and min date results by week and comparing it to the number of dates(DistinctCount) in a if statement so it looks like:

Calc MIN =

if([CountDistinct]<[CountDateDiff],+0, minx(

(KEEPFILTERS(VALUES('DateTable'[DateTime])),

CALCULATE(COUNTA('Table'[ID#])))

For average I am actually going to do a manual count/datediff instead of letting it do the count/distinctcount that it does when using the average command.

[StilHopen]

accepted it as it has been a month 🙂

[AlexisOlson]

AVERAGEX excludes blanks from consideration, so you may want to add zeros like this:

AVERAGEX (
    KEEPFILTERS ( VALUES ( 'DateTable'[DateTime] ) ),
    CALCULATE ( COUNTA ( 'Table'[ID#] ) ) + 0
)

[vanessafvg]

how is your table joined to the datetime? what are you expecting there  a little confused by what you showing as the times are different between the tables.  Can you also show how you are using the visual to get your results?  how are you getting your min and max?   if there is no value ie its blank you can always pad a value to return 0 if blank.

if measure = if(isblank(measure), 0, measure)

[StilHopen]

Joined with a relationship for a many to one bi directional.  This is shown on a line chart showing calculations by grouped by hour of day.  The calculations work but in the way that I get the filtered results even when it is the forumla AlexisOlson showed with the +0 for the average, but the filtering of Orange/ Saturdays / 3am seem to give me the results I described where null weeks are not counted.  I can't use the +0 for min or it returns 0 for everything.

My guess is I will have to calculate the potential number of Saturdays based on the filter and use that to determin the average and something similar for Min where if actual is less then potential then 0 else use formula.