Average Earliest Time by Seller by day of week

[daniel885]

Hello all! I need help with a DAX measure that averages the latest end time of a job by seller. But I need to filter that by day of week as well.

I came up with this DAX measure for averaging latest end times by sellers on Wednesdays but when I do it, it takes the latest end time of each seller across all Wednesdays rather than the average across Wendesdays - so the roll up becomes the average of the latest any one seller completed work on any Wednesday rather than every Wednesday. I need the average of the latest end time PER SELLER PER DAY for just any day where it Wednesday.  I hope that makes sense! 🙂

Avg Latest End Time Wednesdays = VAR RESULT = AVERAGEX (VALUES ([SELLER]),
CALCULATE (MAX([ACTUAL END TIME]),[Name of Day]="Wednesday"))
RETURN IF (NOT (ISBLANK(RESULT)),RESULT+TIME(0,0,0))

Thanks in advance for any help you can give!

[daniel885]

Thank you! I actually just came up with another solution of joining date and sellerID together in another column and using that to take average time and that worked too! I appreciate the help. Just needed some brain storm time!

[FreemanZ]

seems ok. could you also provide some sample data?

[daniel885]

Here is some of the data... I want to take the latest end times by seller (CHID) for each day then average that by day of week.  The formula above is taking the latest end time of each seller regardless of the day (so latest among all Wednesdays) and averaging that.

I want to

1. Take the latest end time by seller for each date

2. Average the above by day of the week

I tried substituting [seller] with [date] in the original formula but that didn't work. It actually gave the same result.

THANKS AGAIN!!

[FreemanZ · Accepted solution]

try like:

Avg Latest End Time Wednesdays = 

VAR RESULT = 

AVERAGEX (

     SUMMARIZE (

         TableName,

         TableName[CHID],

         TableName[Completion Date]

     ),

     CALCULATE (

          MAX([ACTUAL END TIME]),

          [Name of Day]="Wednesday"

     )

)

RETURN IF (NOT (ISBLANK(RESULT)),RESULT+TIME(0,0,0