topic Re: Average of time spent by states in a month in DAX Commands and Tips

Average of time spent by states in a month

Bu__ — Thu, 22 Feb 2024 03:24:55 GMT

I have a data like this,

Month - Year	Sum of Time spent	Count ofID	Avg. Time spent in Hrs	States
Oct-23	55.6	12	4.64	Active (wip)
Oct-23	72.85	12	6.08	Assigned
Oct-23	2.52	7	0.36	New
Oct-23	0.3	2	0.15	Pending - 3rd Party
Oct-23	136.58	9	15.18	Pending - End User
Oct-23	0.15	3	0.05	Rejected - Not responsible
Oct-23	0.18	10	0.02	Resolved - Solved for CU
Oct-23	0.6	4	0.15	Resolved - SP part finished
Total	268.78	12	22.42

Here the avg time spent in hrs is not totalling properly

It's becasue the Distinct count of ID in the month is 12 but here due to states being repeated in every id , count of ID for each state is showing the number of times it is appearing when it should be only 12

HEre i have used a dax measure as shown below to calculate average time spent by states in months

ts_avg_month =
CALCULATE(
AVERAGEX (
SUMMARIZE ('Table',
' date'[date] ,
'Table'[ID],
"Monthly Time spent per ID",
SUM ( 'Table'[Time spent] )
),
[Monthly Time spent per id]
)
) so how do i correct this so that i get the correct averages for each states?

Thanks in advance

Re: Average of time spent by states in a month

some_bih — Thu, 22 Feb 2024 07:13:04 GMT

Hi Bu__

It seems that your you did not include State in your SUMMARIZE table

Try v2 below

ts_avg_month 2=

CALCULATE(
AVERAGEX (
SUMMARIZE ('Table',
' date'[date] ,
'Table'[ID],
'Table'[States],
"Monthly Time spent per ID",SUM ( 'Table'[Time spent] )
),
[Monthly Time spent per id]
)
)

Re: Average of time spent by states in a month

Anonymous — Thu, 29 Feb 2024 02:05:35 GMT

Hi Bu__ ,

Please try the following methods and check if they can solve your problem:

1.Create the simple table.

2.Create the new measure to calculate average.

st_avg_month = VAR Dis_ID = DISTINCTCOUNT('Table'[ID]) RETURN DIVIDE( SUM('Table'[Time spent]), Dis_ID )

3.Drag the measure into the visual. The result is shown below.

Best Regards,

Wisdom Wu

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