Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I've asked for this almost 2 month ago, but not response. I'm posting this again hoping somebody can help me.
I have a table with this columns:
RecordDate | TimeTracked | TeamMember | Activity
I need to get the daily average of time tracked for a user and an activity.
I'm using this measure
CALCULATE(AVERAGEX(VALUES(TimeExport[RecordDate]), TimeExport[TotalTracked]))
but this does not have in account days with no time tracked, this only gives me the average for all days with time tracked.
Here is some example data:
How can I do this?
Thanks in advance.
Hi @rodrigomocca,
According to your requirements, the result, could be a table visual, should contain TeamMember, RecordDate, ActivityType. Please check out the demo here.
BTW, if you can input the sample data in TEXT mode, it could be convenient to others to test.
Best Regards,
Dale
Hi, thanks for your answer.
The solution is not what I'm looking for. Maybe the title is confusing.
I'll give you a more detailed example:
Let's say the user Rodrigo Mocca have the following rows:
Date Team Member Activity Time Tracked
Date | Team Member | Activity | Time Tracked |
03/05/2018 | Rodrigo Mocca | Development | 5 |
03/06/2018 | Rodrigo Mocca | Development | 3 |
03/07/2018 | Rodrigo Mocca | Development | 5 |
03/09/2018 | Rodrigo Mocca | Development | 6 |
In the last 5 days, he only track time for development on 4. What I need to get from here is:
"Rodrigo Mocca spends 3.8 in development by day in average".
This result comes from (5+3+5+6)/5
The calculation I have right now gives me 4.75, because doesn't take in account the empty day.
Do you understand my problem now?
Try with this:
Result = DIVIDE ( SUM ( Table1[Time Tracked] ); DATEDIFF ( MIN ( Table1[Date] ); MAX ( Table1[Date] ); DAY ) + 1 )
Maybe need some adjustment when is just 1 day of Difference or if you want to doesn't cout weekends but this is the logic.
Regards
Victor
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |