Hello Everyone! Penny for your thoughts Fact TableExam Id Report Date Report Hour Employee Location Task Type 1A6/1/202010:00 AMJohn DoeOfficeAB1B6/1/20205:00 AMJane DoeHomeAB1C6/1/202010:00 AMBatmanBat CaveCD1D6/1/20203:00 PMJohn DoeOfficeCD2A6/1/20205:00 PMJane DoeHomeAB2B6/1/20207:00 AMBatmanBat CaveEF2C6/2/20208:00 AMJohn DoeHomeAB2D6/2/20201:00 AMJane DoeBat CaveAB3A6/2/20203:00 PMBatmanOfficeCD3B6/2/20204:00 PMJohn DoeHomeEF3C6/2/20206:00 PMJane DoeBat CaveAB3D6/2/20209:00 AMBatmanOfficeCD5A6/3/20203:00 AMJohn DoeBat CaveCD4B6/3/20205:00 AMJane DoeOfficeEF5C6/3/20207:00 AMBatmanHomeAB4C6/3/202010:00 AMBatmanHomeAB4A6/3/202011:00 AMJohn DoeBat CaveAB5B6/3/20205:00 PMJane DoeOfficeEF6A6/4/20208:00 PMJohn DoeOfficeCD6B6/4/20201:00 AMJane DoeHomeEF6C6/4/20202:00 AMBatmanBat CaveAB7A6/4/20204:00 AMJohn DoeOfficeCD7B6/4/20207:00 PMJane DoeHomeCD7C6/4/20209:00 AMBatmanBat CaveUS8A6/5/202010:00 AMNed FlandersHomeUS8B6/5/20203:00 PMBart SimpsonOfficeAB9A6/6/202011:00 AMJohn DoeHomeEF9B6/6/20205:00 AMJane DoeBat CaveCD9C6/6/202010:00 AMBatmanOfficeCD10A6/7/20203:00 PMJohn DoeHomeAB11A6/8/202012:00 PMJane DoeBat CaveEF11B6/8/20201:00 PMBatmanOfficeAB11C6/8/20208:00 AMJohn DoeBat CaveCD11D6/8/20201:00 AMJane DoeOfficeAB11E6/8/20203:00 PMBatmanHomeEF11F6/8/20204:00 PMJohn DoeBat CaveCD12A6/9/20206:00 PMJane DoeOfficeUS12B6/9/20202:00 PMBatmanHomeCD12C6/9/202011:00 AMJohn DoeOfficeABGoal: Visualize the average number of exams and task types by hourCurrent Approach:Calculate the number the of 'Task Types' filtered by 'Report Hour' and visualize that on a stacked column chart as followsAxis: Report HourLegend: Report TypeValues: Average TaskType by Report HourDAX for Average TaskType by Report HourAverage TaskType by ReportHour = AVERAGEX( 'Fact Table', CALCULATE ( COUNT('Fact Table'[Task Type]), FILTER('Fact Table', 'Fact Table'[Report Hour]) ))Result: I still get a result that shows me the number of Exam Types by Hour. Where did I go wrong? How Can I do this correctly?Data Validation Value:These are the exam frequencies by hour and Task Type over the 9 days covered in the data setI want to know what the average number of exam types by hour. So over 9 days there were 9 different 10:00 AM time windows5 exams occured in that time window that time periodThat means on average there were 0.55 exams in the 10:00 AM time window (5 exams/ 9 days) - 0.22 of them were of type AB (2 exams/9 days) - 0.22 of them were of type CD (2 exams/9 days) - 0.11 of them were of type US (1 exam/9 days)0.22 (AB) + 0.22 (CD) + 0.11 (US) = 0.55 (Total)I would like to visualize there number for each time slot!(EX. What does this look like for 11:00 AM, 12:00 PM, 1:00 PM, etc.)Thank you so much for all your help!!!!

mccollough here are 3 measures to get this going. these are self-explanatory but if you need further details, let me know. Count Exams = COUNTROWS ( Exam1 ) //exam1 is a name of the table in my model Number of Days = CALCULATE ( DISTINCTCOUNT ( Exam1[Report Date] ), ALLSELECTED () ) Avg per Hour = VAR __examDays = Exam1[Number of Days] RETURN SUMX ( SUMMARIZE ( Exam1, Exam1[Report Hour], Exam1[Task Type], "@AVG", DIVIDE ( [Count Exams], __examDays ) ), [@AVG] ) ✨ Follow us on LinkedIn Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation! ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

mccollough

Helper I

5 years ago

Solved

Average Frequency by Hour Calculation

Hello Everyone!

Penny for your thoughts

Fact Table

Exam Id	Report Date	Report Hour	Employee	Location	Task Type
1A	6/1/2020	10:00 AM	John Doe	Office	AB
1B	6/1/2020	5:00 AM	Jane Doe	Home	AB
1C	6/1/2020	10:00 AM	Batman	Bat Cave	CD
1D	6/1/2020	3:00 PM	John Doe	Office	CD
2A	6/1/2020	5:00 PM	Jane Doe	Home	AB
2B	6/1/2020	7:00 AM	Batman	Bat Cave	EF
2C	6/2/2020	8:00 AM	John Doe	Home	AB
2D	6/2/2020	1:00 AM	Jane Doe	Bat Cave	AB
3A	6/2/2020	3:00 PM	Batman	Office	CD
3B	6/2/2020	4:00 PM	John Doe	Home	EF
3C	6/2/2020	6:00 PM	Jane Doe	Bat Cave	AB
3D	6/2/2020	9:00 AM	Batman	Office	CD
5A	6/3/2020	3:00 AM	John Doe	Bat Cave	CD
4B	6/3/2020	5:00 AM	Jane Doe	Office	EF
5C	6/3/2020	7:00 AM	Batman	Home	AB
4C	6/3/2020	10:00 AM	Batman	Home	AB
4A	6/3/2020	11:00 AM	John Doe	Bat Cave	AB
5B	6/3/2020	5:00 PM	Jane Doe	Office	EF
6A	6/4/2020	8:00 PM	John Doe	Office	CD
6B	6/4/2020	1:00 AM	Jane Doe	Home	EF
6C	6/4/2020	2:00 AM	Batman	Bat Cave	AB
7A	6/4/2020	4:00 AM	John Doe	Office	CD
7B	6/4/2020	7:00 PM	Jane Doe	Home	CD
7C	6/4/2020	9:00 AM	Batman	Bat Cave	US
8A	6/5/2020	10:00 AM	Ned Flanders	Home	US
8B	6/5/2020	3:00 PM	Bart Simpson	Office	AB
9A	6/6/2020	11:00 AM	John Doe	Home	EF
9B	6/6/2020	5:00 AM	Jane Doe	Bat Cave	CD
9C	6/6/2020	10:00 AM	Batman	Office	CD
10A	6/7/2020	3:00 PM	John Doe	Home	AB
11A	6/8/2020	12:00 PM	Jane Doe	Bat Cave	EF
11B	6/8/2020	1:00 PM	Batman	Office	AB
11C	6/8/2020	8:00 AM	John Doe	Bat Cave	CD
11D	6/8/2020	1:00 AM	Jane Doe	Office	AB
11E	6/8/2020	3:00 PM	Batman	Home	EF
11F	6/8/2020	4:00 PM	John Doe	Bat Cave	CD
12A	6/9/2020	6:00 PM	Jane Doe	Office	US
12B	6/9/2020	2:00 PM	Batman	Home	CD
12C	6/9/2020	11:00 AM	John Doe	Office	AB

Goal: Visualize the average number of exams and task types by hour

Current Approach:

Calculate the number the of 'Task Types' filtered by 'Report Hour' and visualize that on a stacked column chart as follows
Axis: Report Hour
Legend: Report Type
Values: Average TaskType by Report Hour

DAX for Average TaskType by Report Hour

Average TaskType by ReportHour =

AVERAGEX

(

'Fact Table',

CALCULATE

(

COUNT('Fact Table'[Task Type]),

FILTER('Fact Table', 'Fact Table'[Report Hour])
)
)

Result:

I still get a result that shows me the number of Exam Types by Hour.

Where did I go wrong? How Can I do this correctly?

Data Validation Value:

These are the exam frequencies by hour and Task Type over the 9 days covered in the data set
I want to know what the average number of exam types by hour.

So over 9 days there were 9 different 10:00 AM time windows
5 exams occured in that time window that time period

That means on average there were 0.55 exams in the 10:00 AM time window (5 exams/ 9 days)
- 0.22 of them were of type AB (2 exams/9 days)

- 0.22 of them were of type CD (2 exams/9 days)
- 0.11 of them were of type US (1 exam/9 days)

0.22 (AB) + 0.22 (CD) + 0.11 (US) = 0.55 (Total)

I would like to visualize there number for each time slot!
(EX. What does this look like for 11:00 AM, 12:00 PM, 1:00 PM, etc.)

Thank you so much for all your help!!!!

Need Help

parry2k
5 years ago
mccollough here are 3 measures to get this going. these are self-explanatory but if you need further details, let me know.

Count Exams = COUNTROWS ( Exam1 ) //exam1 is a name of the table in my model Number of Days = CALCULATE ( DISTINCTCOUNT ( Exam1[Report Date] ), ALLSELECTED () ) Avg per Hour = VAR __examDays = Exam1[Number of Days] RETURN SUMX ( SUMMARIZE ( Exam1, Exam1[Report Hour], Exam1[Task Type], "@AVG", DIVIDE ( [Count Exams], __examDays ) ), [@AVG] )

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

6 Replies

parry2k
Super User
5 years ago
mccollough not sure what you want the expected result to be? Can you explain?
- mccollough
  Helper I
  5 years ago
  parry2k
  
  Aha! There's part of the problem!
  I was thinking about it the wrong way.
  I updated the Data validation segment of my post to better reflect what I'm looking for.
  Unfortunately I still don't know how to go about making DAX perform that calculation.
  
  PS
  I also modified the fact table slightly to reflect how the data actually appears in my real dataset
parry2k
Super User
5 years ago
mccollough is this the output you are expecting:
- mccollough
  Helper I
  5 years ago
  parry2k That's exactly what I was looking for! Would you mind breaking down how you accomplished that?
parry2k
Super User
5 years ago
mccollough here are 3 measures to get this going. these are self-explanatory but if you need further details, let me know.

Count Exams = COUNTROWS ( Exam1 ) //exam1 is a name of the table in my model Number of Days = CALCULATE ( DISTINCTCOUNT ( Exam1[Report Date] ), ALLSELECTED () ) Avg per Hour = VAR __examDays = Exam1[Number of Days] RETURN SUMX ( SUMMARIZE ( Exam1, Exam1[Report Hour], Exam1[Task Type], "@AVG", DIVIDE ( [Count Exams], __examDays ) ), [@AVG] )

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- mccollough
  Helper I
  5 years ago
  Worked like a charm!
  Thank you for your help!!!

Forum Discussion

Average Frequency by Hour Calculation

6 Replies

Related content

Calculate Average Order frequency

Calculating Orders Frequency

DAX Measure for Ranking the valid rows and calculating the average rating

Calculating average 12 month average

Calculating Weighted Average