average measure

2 Topics

Adding SWITCH for Average Call Time and Average Hold Time
Hi! I need help adding SWITCH function to two separate DAX measures for a Call Center that calculates "Average Call Time" and "Average Hold Time. There are inactive relationships between Advisor and Date from a Prod Sales Table and Combined Call Logs Table. Please use the same slicer conditions I used in a previous Total Calls measure: Total Calls Dynamic = SWITCH( TRUE(), // Case 1: Single Advisor AND Single Date selected HASONEVALUE(UpdatedProd521[Advisor]) && HASONEVALUE(UpdatedProd521[Date]), CALCULATE( SUM('Combined Call Logs 5/17'[CALLS]), USERELATIONSHIP(UpdatedProd521[Advisor], 'Combined Call Logs 5/17'[ADVISOR]), FILTER( 'Combined Call Logs 5/17', 'Combined Call Logs 5/17'[DATE] = VALUES(UpdatedProd521[Date]) ) ), // Case 2: Only Advisor is selected HASONEVALUE(UpdatedProd521[Advisor]), CALCULATE( SUM('Combined Call Logs 5/17'[CALLS]), USERELATIONSHIP(UpdatedProd521[Advisor], 'Combined Call Logs 5/17'[ADVISOR]) ), // Case 3: Only Date is selected HASONEVALUE(UpdatedProd521[Date]), CALCULATE( SUM('Combined Call Logs 5/17'[CALLS]), USERELATIONSHIP('Combined Call Logs 5/17'[DATE], UpdatedProd521[Date]) ), // Default: sum all calls if no or multiple selections SUM('Combined Call Logs 5/17'[CALLS]) Here is my DAX for average call time (without Switch) and outputs 0 if no call time AverageCallDuration = VAR TotalSeconds = COALESCE(CALCULATE(AVERAGE('Combined Call Logs 5/17'[CALL TIME (SEC)]),USERELATIONSHIP(UpdatedProd521[Advisor], 'Combined Call Logs 5/17'[ADVISOR])),0) //Replace YourTable and SecondsColumn VAR Minutes = INT (TotalSeconds / 60) VAR Seconds = MOD (TotalSeconds, 60) RETURN FORMAT (Minutes, "0") & " min" Here's my DAX for Average Hold Time (without Switch) and outputs 0 if no hold time Avg Hold Time = VAR TotalSeconds = COALESCE(CALCULATE(AVERAGE('Combined Call Logs 5/17'[HOLD TIME (SEC)]),USERELATIONSHIP('UpdatedProd521'[Advisor], 'Combined Call Logs 5/17'[ADVISOR])),0) // Replace YourTable and SecondsColumn VAR Minutes = INT ( TotalSeconds / 60 ) VAR Seconds = MOD ( TotalSeconds, 60 ) RETURN FORMAT ( Minutes, "0" ) & " min, " & FORMAT ( Seconds, "0" ) & " sec"
Solved
jabueg
1 year ago Place DAX Commands and Tips
702Views
2likes
2Comments
DAX for Average the result of the measure by month
Hi I would like to average the result from one of my measure (Column B). According to the above picture, column B is the result from this measure HC-Resign HC = CALCULATE(COUNT('HC'[Employee ID]),'Attrition'[PE]="1") Resign = CALCULATE([Resign], filter(ALL('Data as of_LookUp'[Data as of]),'Data as of_LookUp'[Data as of]<MAX('Data as of_LookUp'[Data as of]))) And after I got the column B, I tried to average them to result in column D, using ALL / FILTER anything that I can think of but it doesn't work. So anyone please give me the light. Thank youu!
Solved
Anonymous
3 years ago Place DAX Commands and Tips
1.2KViews
0likes
4Comments