cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rodrigomocca
Frequent Visitor

Daily Average

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:

2018-02-22_11-29-24.png

 

How can I do this?

 

Thanks in advance.

3 REPLIES 3
v-jiascu-msft
Microsoft
Microsoft

Hi @rodrigomocca,

 

According to your requirements, the result, could be a table visual, should contain TeamMember, RecordDate, ActivityType. Please check out the demo here.

Daily_Average

BTW, if you can input the sample data in TEXT mode, it could be convenient to others to test.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

DateTeam MemberActivityTime Tracked
03/05/2018Rodrigo MoccaDevelopment5
03/06/2018Rodrigo MoccaDevelopment3
03/07/2018Rodrigo MoccaDevelopment5
03/09/2018Rodrigo MoccaDevelopment6

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?

@rodrigomocca

 

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




Lima - Peru

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.