Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey guys,
I have the following three tables. Each participant is in a department and creates learning sessions on a per day basis.
Organization Structure
DepartmentCode | Organisation | Cluster | DepartmentName |
12345 | ABC Mechatronics | Sales | InHouseService |
123456 | ABC Mechatronics | Sales | FieldService |
98765 | ABC Electronics | Technology | Development |
Participants
Participant_ID | Name | DepartmentCode | Hire_Date |
1 | John Doe | 12345 | 01-11-2017 |
2 | Max Mustermann | 12345 | 01-11-2017 |
3 | John Smith | 12345 | 01-11-2017 |
4 | Jan Janssen | 12345 | 01-11-2017 |
5 | John Blow | 123456 | 01-11-2017 |
6 | Juan Pérez | 123456 | 01-11-2017 |
7 | Matti Meikäläinen | 123456 | 01-11-2017 |
8 | Jean Dupont | 98765 | 01-11-2017 |
9 | John Brown | 12345 | 01-12-2017 |
Learning Sessions of the Participants
Participant_ID | Launch_History_id | Launch_Date | Seconds_Spend |
1 | 20984 | 17.11.17 | 444 |
1 | 23271 | 17.11.17 | 24 |
1 | 23273 | 19.11.17 | 974 |
4 | 20970 | 23.11.17 | 439 |
4 | 23301 | 23.11.17 | 1 |
5 | 20935 | 05.11.17 | 84 |
5 | 21819 | 05.11.17 | 28 |
5 | 21817 | 17.11.17 | 72 |
5 | 20983 | 17.11.17 | 373 |
6 | 20981 | 29.11.17 | 10 |
8 | 20987 | 03.11.17 | 225 |
8 | 23378 | 17.11.17 | 148 |
8 | 23386 | 17.11.17 | 4 |
9 | 24764 | 03.12.17 | 567 |
I have created the following visual out of it where the data is filtered for November 2017:
Organisation | Cluster | DepartmentName | #LearnersInParticipants | #LearnersInSessions | Sum_of_Seconds_Spend | Average_Time_Per_Active_User | Average_Time_Per_Users |
ABC Mechatronics | Sales | InHouseService | 5 | 2 | 1882 | 941 | 376,4 |
ABC Mechatronics | Sales | FieldService | 3 | 2 | 567 | 283,5 | 189 |
ABC Electronics | Technology | Development | 1 | 1 | 377 | 377 | 377 |
#LearnersInParticipants = DISTINCTCOUNT(Participants_sample[Participant_ID])
#LearnersInSessions = DISTINCTCOUNT(Learning_Results_Single_Sessions_sample[Participant_ID])
What I have done:
I have calculated the average time for all users who actually have sessions.
Average_Time_Per_Active_User = Sum(Learning_Results_Single_Sessions_sample[Seconds_Spend])/DISTINCTCOUNT(Learning_Results_Single_Sessions_sample[Participant_ID])
To compare the departments I have calculated the sum of seconds spend divided by all users in departments, regardless of whether some have sessions or not.
Average_Time_Per_Users = Sum(Learning_Results_Single_Sessions_sample[Seconds_Spend])/DISTINCTCOUNT(Participants_sample[Participant_ID])
What I want:
Actually, the calculation for all department members is currently not correct. My colleague are going to filter for time periods.. Actually, I have to consider the hire-date of the participants then as well.
For example: If I filter for November 2017, the shown average for department "InHouseService" is 376,4 (1882 sum of seconds /5 learners). Actually, this value should be 470,5 --> 1881/4 learners because John Brown was not part of the department in November yet. He started in December. The average has to be calculated with the users who were actually part of the department at the specific point in time.
This is maybe not really a PowerBi question, more a database design question, but can you give me a hint how I can determine the amount of all participants in a department regarding their hire date in combination with the learning sessions?
I would really appreciate some advice.
Solved! Go to Solution.
You may take a look at this discussion.
http://community.powerbi.com/t5/Desktop/Relational-Date-Filter-By-Other-Date-How-To/td-p/298373
You may take a look at this discussion.
http://community.powerbi.com/t5/Desktop/Relational-Date-Filter-By-Other-Date-How-To/td-p/298373
*PUSH* due to the fact that the topic was blocked because of false spam positive.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |