Forum Discussion

rodrigomocca's avatar
rodrigomocca
Frequent Visitor
8 years ago

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.

Need Help

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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

    • rodrigomocca's avatar
      rodrigomocca
      Frequent Visitor
      8 years ago

      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?

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        8 years ago

        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