Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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:
(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.
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:
(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.
accepted it as it has been a month 🙂
AVERAGEX excludes blanks from consideration, so you may want to add zeros like this:
AVERAGEX (
KEEPFILTERS ( VALUES ( 'DateTable'[DateTime] ) ),
CALCULATE ( COUNTA ( 'Table'[ID#] ) ) + 0
)
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)
Proud to be a Super User!
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.
User | Count |
---|---|
107 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |