topic Re: How to get AVERAGE or SUM for a measure in a summarized table? in DAX Commands and Tips

How to get AVERAGE or SUM for a measure in a summarized table?

bergen288 — Thu, 13 Apr 2023 13:33:27 GMT

I have a summarized table from my fact table as below and its Case_ID has 1:* relationship with fact table.

SummarizedTable =

SUMMARIZE (

'PBI_XZ_Case_Time_Session',

'PBI_XZ_Case_Time_Session'[Case_ID],

"# of Assignees", DISTINCTCOUNTNOBLANK('PBI_XZ_Case_Time_Session'[Created_By])

)

The purpose is to get the right hand table visual below:

Below are measures for each column:

Session Hours = SUM( 'PBI_XZ_Case_Time_Session'[Session Seconds] ) / 3600

Session Minutes = SUM( 'PBI_XZ_Case_Time_Session'[Session Seconds] ) / 60

Number of Cases = DISTINCTCOUNTNOBLANK('PBI_XZ_Case_Time_Session'[Case_ID])

Session Minutes per Case = [Session Minutes] / [Number of Cases]

Case Elapsed Days =

VAR __start = MIN ( 'PBI_XZ_Case_Time_Session'[Created_Date] )

VAR __end = MAX ( 'PBI_XZ_Case_Time_Session'[Created_Date] )

VAR __Days = DATEDIFF(__start, __end, DAY)

RETURN

__Days

All other measures are good in my target table, but Case Elapsed Days gives me the Max value instead of AVG or SUM. How may I get its AVERAGE or SUM value in my target table?

Re: How to get AVERAGE or SUM for a measure in a summarized table?

yyzheng12 — Thu, 13 Apr 2023 13:45:45 GMT

MAX and MIN work exactly as they are supposed to. They look for the maximum and minimum values in a filter context. Can you define Case Elapse Days more clearly?

Re: How to get AVERAGE or SUM for a measure in a summarized table?

bergen288 — Thu, 13 Apr 2023 18:34:19 GMT

For any particular Case_ID at the left table in my screenshot above, Case Elapse Days is the date difference between ticket start date and ticket end date. However, when ticket is grouped by "# of Assignees" at the right table, Case Elapse Days is the date difference between earliest start date and latest end date in the group, most likely from different tickets. But that is NOT what I want. I simply want the AVERAGE or SUM values of Case Elapse Days at the right table. For example, for yellow highlighted group 29, I want either SUM of (56, 53) or AVG of (56, 53). Actually, AVG is better in my case. What's the best way to address it?

Thanks.

Re: How to get AVERAGE or SUM for a measure in a summarized table?

bergen288 — Thu, 13 Apr 2023 18:46:49 GMT

I am thinking about create a new "Case Elapse Days" column in my SummarizedTable for each case. However, if I use the same Case Elapse Days measure formular to create column, every ticket has 59 max value. How do I get the correct value for each Case?

Re: How to get AVERAGE or SUM for a measure in a summarized table?

bergen288 — Thu, 13 Apr 2023 19:13:14 GMT

I figured out how to make it work. Below is my new SummarizedTable. Its [Elapsed Days] column is the same as "Case Elapse Days" measure for each Case.

SummarizedTable =

SUMMARIZE (

'PBI_XZ_Case_Time_Session',

'PBI_XZ_Case_Time_Session'[Case_ID],

"# of Assignees", DISTINCTCOUNTNOBLANK('PBI_XZ_Case_Time_Session'[Created_By]),

"Elapsed Days", DATEDIFF(MIN ( 'PBI_XZ_Case_Time_Session'[Created_Date] ), MAX ( 'PBI_XZ_Case_Time_Session'[Created_Date] ), DAY)

)

Next is new meaure [Avg Elapsed Days] which can be used correctly in my target table visual.

Avg Elapsed Days =

AVERAGE (SummarizedTable[Elapsed Days])